Top users and how frequently they hit reputation cap


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 (5) 1-sigma Poisson uncertainties in yearly rep cap rate (Forked from

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

with topusers as (select top 200 
                    , datediff(d , creationdate, getdate()) as Daysmember
             from users 
             order by reputation desc) ,
voting as (
                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
                        WHEN 3 THEN -2  -- downvote
                        WHEN 9 THEN BountyAmount -- collected bounty
                 END) as Rep,
                 Votes.CreationDate AS CreationDate,
          FROM   Posts
                 INNER JOIN Votes              
                   ON Votes.PostId = Posts.Id
                 INNER JOIN topusers t 
                    on = Posts.OwnerUserId 
          WHERE ( Posts.CommunityOwnedDate IS NULL ) 
          GROUP BY Votes.CreationDate,
bounty as (
                SUM(-BountyAmount) as Rep,  -- Bounty given
                Votes.CreationDate AS CreationDate,
                Votes.UserID as OwnerUserId
          FROM  Votes                    
          INNER JOIN topusers t on =  Votes.UserID
          WHERE votes.votetypeid = 8
          GROUP BY Votes.CreationDate,
sugedit as (  -- suggested edits
                 COUNT(*) * 2 as Rep 
               , cast(ApprovalDate as date) As CreationDate
               , OwnerUserId
          FROM  SuggestedEdits 
          INNER JOIN topusers t on =  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 Caps per Year]
, cast(sqrt(count(*))/(cast(min(daysmember) as float)/365.0) as numeric(5,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
    select b.creationdate
          , b.owneruserid
          , 0
          , b.rep
          , 0
    from bounty b
    select s.creationdate
         , s.owneruserid
         , 0
         , 0
         , s.rep
    from sugedit s 
    ) as ingroup
    group by creationdate,
    having sum(votesrep + bountyrep + sugedtrep)>=200
    ) cntdays
inner join topusers tu on = userid
group by userid
order by count(*) desc

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results