Q&A for ebook publishers and readers
declare @minrep as int = ##minrep:int?10000##; with TopUsers as ( select Id as TopUser, DisplayName from Users where Reputation >= @minrep ), RepDays as ( --Approved suggested edits. select TopUser, Convert(Date, se.ApprovalDate) as tstamp, sum(2) as rep from TopUsers inner join 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 Q's and A's 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 Posts p on TopUser = p.OwnerUserId and p.PostTypeId in (1,2) inner join 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 Votes v on TopUser = v.UserId and v.VoteTypeId = 8 group by TopUser, Convert(Date, v.CreationDate) ), RepCumulative as ( select TopUser, tstamp, rep, sum (rep) over (partition by TopUser order by tstamp) as CumRep from RepDays ), CarryOver as ( select TopUser, min(tstamp) as tstamp from RepCumulative where CumRep >= @minrep group by TopUser ) select rc.TopUser, rc.tstamp, rc.CumRep from RepCumulative as rc inner join CarryOver co on rc.TopUser = co.TopUser and rc.tstamp = co.tstamp order by rc.tstamp