the real legendary badge query


Please login or register to vote for this query.

(click on this box to dismiss)

according to


Q&A for those using, extending or developing Emacs

-- the real legendary badge query
-- according to

;with posts2votes as
  p.owneruserid targetuserid,
  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 = v.postid
inner join users u on = p.owneruserid
where v.votetypeid in (1,2,3,8,9)
  and p.communityowneddate is null

union all

from votes v
inner join posts p1 on = v.postid
  and v.votetypeid = 1 -- question accepted by asker
inner join posts p2 on = p1.parentid
inner join users u on = p2.owneruserid
where p2.communityowneddate is null
-- above this line the CTE attempts to rebuild the posts2votes view/table

SELECT as [User Link],, x.caps
FROM   users u WITH (nolock)
       /* 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
                GROUP  BY targetuserid, 
                          CONVERT(CHAR(8), creationdate, 112) 
                HAVING SUM(targetrepchange) >= 200) AS x 
        GROUP  BY targetuserid 
        HAVING targetuserid=##UserId##) x 
    ON = x.userid 
ORDER BY x.caps desc;

Enter Parameters

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