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)

History of Science and Math

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

with topusers as (select top 1000 
                    , 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]
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