0

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 those using, extending or developing Emacs

-- 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 >= 1000 -- 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 >= 1000 -- quick pre-filter
)
-- above this line the CTE attempts to rebuild the posts2votes view/table

SELECT u.id as [User Link], u.id 
FROM   users u WITH (nolock), 
       /* return all userids who have hit the rep cap for 150 days */ 
       (SELECT targetuserid AS userid 
        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(*) >= 150) x 
WHERE  u.id NOT IN /* return all userids who do NOT have the badge */ 
              (SELECT userid 
               FROM badges b 
               WHERE b.name = 'Legendary' 
              ) 
       AND u.id = x.userid 

view current version

edited feb 22 11

created feb 22 11 cyberkiwi2010