Top users by reputation with rep cap and member for days


Please login or register to vote for this query.

(click on this box to dismiss) 1.The top 20 users on a given Stack Exchange community 2.How many days they've been a member 3.How many days they've hit the reputation cap (e.g. 200 points)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

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 sites:
loading Hold tight while we fetch your results