0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners of the art and science of signal, image and video processing

create table #sitestats (
    Site NVarchar(250),
    DName NVarchar(100),
    UserName NVarchar(50),
    UserId int,
    FirstDate Date,
    MadeIt Date,
    Reputation int);
;
with FirstDay as (
    select Convert(Date, min (CreationDate)) as FirstDate,
           'http://codereview.stackexchange.com' as Site,
           'Codereview' as DName
    from [StackExchange.Codereview]..Users
), TopUsers as (
    select Id as TopUser, DisplayName
    from [StackExchange.Codereview]..Users
    where Reputation >= 10000
    
), RepDays as (
            --Approved suggested edits.
            select TopUser, 
                   Convert(Date, se.ApprovalDate) as tstamp,
                   sum(2) as rep
            from TopUsers inner join [StackExchange.Codereview]..SuggestedEdits se on TopUser = se.OwnerUserId
              and se.ApprovalDate is not null
            group by TopUser, Convert(Date, se.ApprovalDate)
              
         UNION ALL
      
            -- Up and Down votes on Qs and As
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(
                   case when v.VoteTypeId = 1 then 15
                        when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        when v.VoteTypeId = 9 then IsNull(v.BountyAmount, 0)
                        else 0
                   end) as rep
            from TopUsers
            inner join [StackExchange.Codereview]..Posts p on TopUser = p.OwnerUserId
              and p.PostTypeId in (1,2)
            inner join [StackExchange.Codereview]..Votes v on p.Id = v.PostId
              and v.VoteTypeId in (1,2,3,9)
            group by TopUser, Convert(Date, v.CreationDate)
              
         UNION ALL
         
            -- Bounties that were offered....
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(-1 * v.BountyAmount) as rep
            from TopUsers inner join [StackExchange.Codereview]..Votes v on TopUser = v.UserId
              and v.VoteTypeId = 8
            group by TopUser, Convert(Date, v.CreationDate)
), RepCumulative as (
    select TopUser,
           tstamp,
           sum (sum(rep)) over (partition by TopUser order by tstamp) as CumRep
    from RepDays
    group by TopUser, tstamp
), CarryOver as (
    select TopUser, min(tstamp) as tstamp
    from RepCumulative
    where CumRep >= 10000
    group by TopUser
)
insert into #sitestats
select Top 3
       Site as Site,
       DName as DName,
       u.DisplayName as UserName,
       u.Id as UserId,
       FirstDate as FirstDate,
       rc.tstamp as MadeIt,
       rc.CumRep as Reputation
from RepCumulative as rc
   inner join CarryOver co on
       rc.TopUser = co.TopUser
   and rc.tstamp = co.tstamp
   inner join [StackExchange.Codereview]..Users u on
       rc.TopUser = u.Id
   inner join FirstDay on 1 = 1
order by rc.tstamp
select * from #sitestats

view current version

edited dec 11 14

created jan 17 14 rolfl