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 (Forked from

Stack Overflow

Q&A for professional and enthusiast programmers

with 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
          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                    
          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 
          WHERE ApprovalDate IS NOT NULL
          GROUP BY cast(ApprovalDate as date)
                 , OwnerUserId
topusers as (select top 200 
                   , creationdate
             from users 
             order by reputation desc)
select UserId as [User Link]
, Count(RepCapdate) as [Days Rep cap hit]
, DATEDIFF( d, min(creationdate), GetDate()) as [Member for days]
from (
    select as userid
         , coalesce( v.creationdate
                   , b.creationdate
                   , s.creationdate
                   , u.creationdate ) as RepCapDate
         , min(u.creationdate)  as creationdate
         , sum( IsNull(v.rep,0)
              + IsNull(b.rep,0)
              + IsNull(s.rep,0)) as rep
    from  topusers U
    left outer join voting v on v.owneruserid =
    left outer join bounty b on b.owneruserid =
    left outer join sugedit s on s.owneruserid =
    group by
           , coalesce( v.creationdate
                     , b.creationdate
                     , s.creationdate
                     , u.creationdate )
    having sum( IsNull(v.rep,0)
              + IsNull(b.rep,0)
              + IsNull(s.rep,0)) > 200
    ) as repcapperday
group by UserId
order by Count(RepCapDate) desc

Enter Parameters

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