Top users rep per x weeks

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

select sum(pst.score)
      , QA
      , usr.displayname
      , sum(upv) as uv
      , sum(downv) as dv
from (
select  p.id as postid
       , case when p.posttypeid = 1 then 'Q' else 'A' end as QA
       , min(p.owneruserid) as uid
       , count(*) as upv
       , 0 as downv
from posts p 
inner join votes v on v.postid = p.id
where v.votetypeid = 2
and v.creationdate between  Dateadd(ww, -2, getdate()) and getdate()
group by p.posttypeid,  p.id
union
select p.id
       , case when p.posttypeid = 1 then 'Q' else 'A' end as QA
       , min(p.owneruserid)
       , 0 as upv2
       , count(*)  as dv2
from posts p 
inner join votes v on v.postid = p.id
where v.votetypeid = 3
and v.creationdate between  Dateadd(ww, -2, getdate()) and getdate()
group by p.posttypeid, p.id
) as fact
inner join users usr on fact.uid = usr.id
inner join posts pst on fact.postid = pst.id
group by qa, usr.displayname
order by usr.displayname, QA

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results