;with posts2votes as ( select p.owneruserid targetuserid...


Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

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

-- Enter Query Title
-- Enter Query Description

;with posts2votes as
  p.owneruserid targetuserid,
  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

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 = x.userid 

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results