0

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 http://meta.stackexchange.com/questions/234720/custom-query-data-explorer-rep)

Q&A for professional and enthusiast programmers

;
with voting as (
         SELECT 
                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,
                 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,
                Votes.CreationDate AS CreationDate,
                Votes.UserID as OwnerUserId
          FROM  Votes                    
          WHERE votes.votetypeid = 8
          GROUP BY Votes.CreationDate,
                Votes.UserID
),
sugedit as (
          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

view current version

edited jun 28 14

created jun 28 14 rene