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 10  -- upvote
                        WHEN 3 THEN -2  -- downvote
                        WHEN 9 THEN BountyAmount
                 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,
                Votes.CreationDate AS CreationDate,
                Votes.UserID as OwnerUserId
          FROM  Votes                    
          WHERE votes.votetypeid = 8
          GROUP BY Votes.CreationDate,
sugedit as (
                COUNT(*) * 2 as Rep ,
                cast(ApprovalDate as date) As CreationDate,
          FROM  SuggestedEdits 
          WHERE ApprovalDate IS NOT NULL
          GROUP BY cast(ApprovalDate as date)
          ,     OwnerUserId
topusers as (select top 200 *
             from users 
             order by reputation desc)
select UserId as [User Link]
, Count(RepCapdate)
from (
    select as userid
  , coalesce( v.creationdate
            , b.creationdate
            , s.creationdate
            , u.creationdate ) as RepCapDate
  , 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