http://data.stackexchange.com/stackoverflow/s/1004/the-real-legendary-badge-query
-- the real legendary badge query
-- according to http://meta.stackexchange.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
