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