0

Please login or register to vote for this query.

(click on this box to dismiss)

Days counting towards Legendary badge

Q&A for enthusiasts and power users of the Android operating system

-- Legendary
-- Days counting towards Legendary badge

;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 targetuserid = 772035
                       --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) x 
WHERE  u.id = 772035

fork query

created aug 13 11 anonymous