0

Please login or register to vote for this query.

(click on this box to dismiss)

Perguntas e respostas para programadores profissionais e entusiastas

with Edits as (
  select distinct
    h.Id,
    h.UserId,
    h.PostId,
    h.CreationDate
  from PostHistory h
  where
    h.PostHistoryTypeId = 5
),

EditVotes as (
  select distinct
    e.UserId,
    count(e.Id) [Edits],
    count(e.PostId) [Posts],
    count(distinct case when dv.Id is null and v.VoteTypeId = 2
          then v.Id else null end) [Upvotes],

    count(distinct case when dv.Id is null and v.VoteTypeId = 1
          then v.Id else null end) [Accepts],

    count(distinct case when dv.Id is not null and v.VoteTypeId = 2
          then v.Id else null end) [DvUpvotes],

    count(distinct case when dv.Id is not null and v.VoteTypeId = 1
          then v.Id else null end) [DvAccepts]
  from Edits e
    inner join Votes v on
      v.PostId = e.PostId and
      v.CreationDate > e.CreationDate and
      v.VoteTypeId in (1,2)
      and not exists (
        select 1 from Votes pv
        where
          pv.PostId = e.PostId and
          pv.VoteTypeId in (1,2) and
          pv.CreationDate < e.CreationDate
      )
      left join Votes dv on
        dv.PostId = e.PostId and
        dv.UserId <> e.UserId and
        dv.VoteTypeId = 3 and
        dv.CreationDate < e.CreationDate
  group by
    e.UserId
)

select
  u.Id [User Link],  
  e.Upvotes + 5*Accepts + 10*DvUpvotes + 15*DvAccepts  [Score]
from EditVotes e
  inner join Users u on e.UserId = u.Id
where
  (e.Upvotes + 5*Accepts + 10*DvUpvotes + 15*DvAccepts) > 6
order by Score desc

fork query

edited aug 22 17

created aug 22 17 anonymous