needs work

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

;with tagorder as (
  select t.id 
  , row_number() over(order by count(*) desc) as rank
  from tags t
  inner join posttags pt on pt.tagid = t.id
  group by t.id
),
mostrep as (
  select p.owneruserid
       , sum(case v.votetypeid when 2 then 
             case p.posttypeid when 1 then 5 else 10 end
             else 0 end) as upv
       , sum(case v.votetypeid when 3 then -2 else 0 end) as dov
       , sum(case v.votetypeid when 1 then 15 else 0 end) as acc
from posts p 
inner join votes v on v.postid = p.id
where v.creationdate between ##start:string## 
                         and ##end:string##
and   v.votetypeid in (1,2,3)
group by p.owneruserid 
)

select owneruserid as [User Link] 
from mostrep
order by upv+dov+acc desc

/*
select p.owneruserid as [User Link]
     , t.tagname
     , rank
     , count(*) as [cnt]
     , sum(case v.votetypeid when 2 then 
           case p.posttypeid when 1 then 5 else 10 end
           else 0 end) as upv
     , sum(case v.votetypeid when 3 then -2 else 0 end) as dov
     , sum(case v.votetypeid when 1 then 15 else 0 end) as acc
     ,sum(case v.votetypeid when 2 then 
           case p.posttypeid when 1 then 5 else 10 end
           else 0 end) 
       + sum(case v.votetypeid when 3 then -2 else 0 end)
       + sum(case v.votetypeid when 1 then 15 else 0 end) as tot
from tags t
inner join tagorder tor on t.id = tor.id
inner join posttags pt on pt.tagid = t.id
inner join posts p on pt.postid in (p.id, p.parentid)
inner join votes v on v.postid = p.id
where v.creationdate between ##start:string## 
                         and ##end:string##
and   v.votetypeid in (1,2,3)
group by p.owneruserid 
       , t.tagname
       , rank
order by sum(case v.votetypeid when 2 then 
           case p.posttypeid when 1 then 5 else 10 end
           else 0 end) 
       + sum(case v.votetypeid when 3 then -2 else 0 end)
       + sum(case v.votetypeid when 1 then 15 else 0 end) desc
     , rank desc
     
     */

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results