SEDE translation of Legendary badge query

Question

Jeff's query for Legendary badge is given here:

https://meta.stackexchange.com/questions/57232/is-150-rep-cap-exceeded-alone-enough-for-legendary-badge/58192#58192

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

Answer 1

Here's my attempt

https://data.stackexchange.com/stackoverflow/s/1004/the-real-legendary-badge-query

Note - There is a discrepancy against the real query, the -1s (downvote) are missing from SEDE

Reproduced below

-- 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