Jeff's query for Legendary badge is given here:
Given that the tables in data.se != tables in production-db, what is the correct translation of this query? Are bounties included as well in "targetrepchange", in particular does offered bounties mean you need to recover that to end the (UTC-based) day with
rep-change(day) > 200?
Somewhat related to: Actual badge queries used by StackExchange for use on SEDE
SELECT 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 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 users2badges u2b WITH (nolock) INNER JOIN badges b ON u2b.badgeid = b.id WHERE b.name = 'Legendary' ) AND u.id = x.userid