0

Please login or register to vote for this query.

(click on this box to dismiss)

(1) The top 200 users on a given Stack Exchange community; (2) How many days they've hit the reputation cap (200 points) (3) How many days they've been a member; (4) Average number of rep caps per 365 days (Forked from http://meta.stackexchange.com/questions/234720/custom-query-data-explorer-rep)

Q&A for professional and enthusiast programmers

;
with topusers as (select top 200 
                    id
                    , datediff(d , creationdate, getdate()) as Daysmember
             from users 
             order by reputation desc) ,
voting as (
         SELECT 
                SUM(case votes.votetypeid 
                        WHEN 1 THEN 15  -- accept
                        WHEN 2 THEN   -- upvote
                           CASE posts.posttypeid 
                              WHEN 1 THEN 5  -- upvote question
                              WHEN 2 THEN 10  -- upvote answer
                           END
                        WHEN 3 THEN -2  -- downvote
                        WHEN 9 THEN BountyAmount -- collected bounty
                 END) as Rep,
                 Votes.CreationDate AS CreationDate,
                 Posts.OwnerUserId 
          FROM   Posts
                 INNER JOIN Votes              
                   ON Votes.PostId = Posts.Id
                 INNER JOIN topusers t 
                    on t.id = Posts.OwnerUserId 
                      
          WHERE ( Posts.CommunityOwnedDate IS NULL ) 
          GROUP BY Votes.CreationDate,
                    Posts.OwnerUserId
),
bounty as (
         SELECT 
                SUM(-BountyAmount) as Rep,  -- Bounty given
                Votes.CreationDate AS CreationDate,
                Votes.UserID as OwnerUserId
          FROM  Votes                    
          INNER JOIN topusers t on t.id =  Votes.UserID
          WHERE votes.votetypeid = 8
          GROUP BY Votes.CreationDate,
                Votes.UserID
),
sugedit as (  -- suggested edits
          SELECT 
                 COUNT(*) * 2 as Rep 
               , cast(ApprovalDate as date) As CreationDate
               , OwnerUserId
          FROM  SuggestedEdits 
          INNER JOIN topusers t on t.id =  SuggestedEdits.OwnerUserId 
          WHERE ApprovalDate IS NOT NULL
          GROUP BY cast(ApprovalDate as date)
                 , OwnerUserId
)
select userid as [User Link]
, count(*) as [Days of Rep Cap]
, min(daysmember) [Days a Member]
, cast(cast(count(*) as float)/(cast(min(daysmember) as float)/365.0) as numeric(5,2)) as [Rep Cap per Year]
, round(sqrt(count(*))/(cast(min(daysmember) as float)/365.0), 2) as [Poisson Uncertainty in Rate]
from (
select creationdate
       , userid
       , sum(votesrep + bountyrep + sugedtrep) as rep
    from (
    select v.creationdate as creationdate
         , v.owneruserid as userid
         , v.rep as votesrep
         , 0 as bountyrep
         , 0 as sugedtrep
    from voting v
    union
    select b.creationdate
          , b.owneruserid
          , 0
          , b.rep
          , 0
    from bounty b
    union 
    select s.creationdate
         , s.owneruserid
         , 0
         , 0
         , s.rep
    from sugedit s 
    ) as ingroup
    group by creationdate,
    userid
    having sum(votesrep + bountyrep + sugedtrep)>=200
    ) cntdays
inner join topusers tu on tu.id = userid
group by userid
order by count(*) desc

fork query

edited aug 11 14 Chris White

created jun 28 14 rene