;with all_dupehammered as ( select distinct b.userid , a.id

0

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 all_dupehammered as
(
select distinct 
       b.userid
     , a.id
     , q.answercount
     , text
     , (case 
      when patindex('%[[]{"Id":'+ cast(b.userid as nvarchar) + ',%', text) > 0
      then 1 
      else 0
      end) as firstvoter
from badges b 
inner join tags t on t.tagname = b.name
inner join posts a on b.userid = a.owneruserid
inner join posts q on q.id = a.parentid
inner join posttags pt on pt.postid = q.id and pt.tagid = t.id
inner join posthistory ph on ph.postid = q.id
where b.tagbased = 1 
and b.class =1 
and a.posttypeid = 2 -- A
and q.posttypeid = 1 -- Q
and q.closeddate is not null 
and a.communityowneddate is not null
and ph.posthistorytypeid = 10 
and text like '%"Id":' + cast(b.userid as nvarchar) + ',%'
and text like '%GoldTagBadge%'
and ph.creationdate > b.date
)

select userid as [User Link]
     , count(*) as[#num dupe hammered]
     , avg(answercount) [avg count]
     , sum(firstvoter) as [# first close vote]
from all_dupehammered
group by userid
order by avg(answercount)
       , count(*) desc

Enter Parameters

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