Top 10 users who commented the most of all question posts given a specific tag and comment duration

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

select TOP 10 c.userid AS "USER ID", count(*) AS TOTAL
 from  Comments c,
(
select  p1.id
  from  Posts p1, PostTags ptags1, Tags t1, PostTypes ptypes1 
 where  p1.posttypeid = ptypes1.id
   and  ptypes1.id = 1
   and  p1.id = ptags1.postid 
   and  ptags1.tagid = t1.id 
   and  t1.tagname = '##TagName##'
union
select  p2.id
  from  Posts p2, PostTypes ptypes2 
 where  p2.posttypeid = ptypes2.id
   and  ptypes2.id = 2
   and  p2.parentid in
     (  select  p3.id
          from  Posts p3, PostTags ptags3, Tags t3, PostTypes ptypes3 
         where  p3.posttypeid = ptypes3.id
           and  ptypes3.id = 1
           and  p3.id = ptags3.postid 
           and  ptags3.tagid = t3.id 
           and  t3.tagname = '##TagName##'
     )
) temp_p
where  temp_p.id = c.postid
  and  c.creationdate >= '##startdate##'
  and  c.creationdate <= '##enddate##'
group by c.userid
order by TOTAL DESC

Enter Parameters

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