Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure
; 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 ) 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