Top users by reputation with rep cap and member for days

4

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/234720/custom-query-data-explorer-rep 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 (
         SELECT 
                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
                           END
                        WHEN 3 THEN -2  -- downvote
                        WHEN 9 THEN BountyAmount -- collected bounty
                 END) as Rep,
                 Votes.CreationDate AS CreationDate,
                 Posts.OwnerUserId 
          FROM   Posts
                 INNER JOIN Votes
                 ON Votes.PostId = Posts.Id
                      
          WHERE ( Posts.CommunityOwnedDate IS NULL ) 
          GROUP BY Votes.CreationDate,
                    Posts.OwnerUserId
),
bounty as (
         SELECT 
                SUM(-BountyAmount) as Rep,  -- Bounty given
                Votes.CreationDate AS CreationDate,
                Votes.UserID as OwnerUserId
          FROM  Votes                    
          WHERE votes.votetypeid = 8
          GROUP BY Votes.CreationDate,
                Votes.UserID
),
sugedit as (  -- suggested edits
          SELECT 
                 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 
                    id
                   , 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 U.id 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 = u.id
    left outer join bounty b on b.owneruserid = u.id
    left outer join sugedit s on s.owneruserid = u.id
    group by U.id
           , 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

Options:
Switch sites:
loading Hold tight while we fetch your results