4

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 (5) 1-sigma Poisson uncertainties in yearly rep cap rate (Forked from http://meta.stackexchange.com/questions/234720/custom-query-data-explorer-rep)

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

;
with voting as (
         SELECT 
                SUM(case votes.votetypeid 
                        WHEN 1 THEN 15
                        WHEN 2 THEN 10 
                        WHEN 3 THEN -3
                        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 *
             from users 
             order by reputation desc)
select UserId as [User Link]
, Count(RepCapdate)
from (
    select U.id as userid
, coalesce( v.creationdate
          , b.creationdate
          , s.creationdate
          , u.creationdate ) as RepCapDate
, sum(v.rep+b.rep+s.rep) 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(v.rep+b.rep+s.rep) > 200
) as repcapperday
group by UserId
order by Count(RepCapDate) desc

view current version

edited jun 28 14

created jun 28 14 rene