2

Please login or register to vote for this query.

(click on this box to dismiss)

according to http://meta.stackoverflow.com/q/58192

Q&A for Ubuntu users and developers

-- the real legendary badge query
-- according to http://meta.stackoverflow.com/q/58192

;with posts2votes as
(
select
  p.owneruserid targetuserid,
  v.creationdate,
  case votetypeid
      when 1 then 15
      when 8 then -bountyamount
      when 9 then bountyamount
      when 2 then 10
      when 3 then -2
      end targetrepchange
from votes v
inner join posts p on p.id = v.postid
inner join users u on u.id = p.owneruserid
where v.votetypeid in (1,2,3,8,9)
  and p.communityowneddate is null
  and u.reputation >= 30000 -- quick pre-filter

union all

select
  p2.owneruserid,
  v.creationdate,
  2
from votes v
inner join posts p1 on p1.id = v.postid
  and v.votetypeid = 1 -- question accepted by asker
inner join posts p2 on p2.id = p1.parentid
inner join users u on u.id = p2.owneruserid
where p2.communityowneddate is null
  and u.reputation >= 30000 -- quick pre-filter
)
-- above this line the CTE attempts to rebuild the posts2votes view/table

SELECT u.id as [User Link], u.id, x.caps
FROM   users u WITH (nolock)
JOIN
       /* return all userids who have hit the rep cap for 150 days */ 
       (SELECT targetuserid AS userid, COUNT(*) caps
        FROM   (SELECT targetuserid, 
                       CONVERT(CHAR(8), creationdate, 112) AS dayperiod, 
                       SUM(targetrepchange)                AS dayrep 
                FROM   posts2votes 
                WHERE  targetrepchange IS NOT NULL 
                       --AND deletiondate IS NULL (deleted data not in data.se)
                GROUP  BY targetuserid, 
                          CONVERT(CHAR(8), creationdate, 112) 
                HAVING SUM(targetrepchange) >= 200) AS x 
        GROUP  BY targetuserid 
        HAVING COUNT(*) >= 130 OR targetuserid=##UserId##) x 
    ON  u.id = x.userid 
ORDER BY x.caps desc;

fork query

edited nov 16 12

created feb 22 11 cyberkiwi2010