Jeff's query for Legendary badge is given here:
Is 150 "rep cap exceeded" alone enough for Legendary badge?
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
Query reproduced
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
