Calculate a number of user's reputation growth over a specified period of time, plot the graphs.
Q&A for enthusiasts and power users of the Android operating system
declare @days as int = ##Days:int?90## declare @userred as nvarchar(40) = ##UserRed:string## declare @usergreen as nvarchar(40) = ##UserGreen:string?Green## declare @userblue as nvarchar(40) = ##UserBlue:string?Blue## declare @userbrown as nvarchar(40) = ##UserBrown:string?Brown## declare @usercyan as nvarchar(40) = ##UserCyan:string?Cyan## declare @nexus as date = dateadd(day, - @days, CURRENT_TIMESTAMP) declare @userid as int declare @userrep as int declare @username as nvarchar(40) declare @epoch as date = dateadd(day, - 1, @nexus) declare @pdate as date declare @rdate as date declare @rtime as datetime declare @raction as nvarchar(10) declare @rrep as int declare @rcnt as int declare @rtmp as int declare @message as nvarchar(250) declare @cumulative as int declare @rollingrep as int declare @rollingbonus as int declare @rollingcap as int ; create table #REPDAY ( UserName nvarchar(40), RepDate date not null, RepWeek date not null, CapLimited int not null, UnCapped int not null, Discarded int not null, Cumulative int not null) ; declare TOPUSERS cursor for select u.Id as UserId, u.DisplayName as Name from Users u where u.DisplayName in (@userred, @usergreen, @userblue, @usercyan, @userbrown) and not exists ( select s.Id from Users s where s.Id != u.Id and s.Reputation > u.Reputation and s.DisplayName = u.DisplayName) open TOPUSERS fetch next from TOPUSERS into @userid, @username while @@FETCH_STATUS = 0 begin -- for each user, reset the accumulators select @pdate = @epoch, -- something different @rcnt = 0, @rollingrep = 0, @rollingbonus = 0, @rollingcap = 0, @cumulative = 0 declare USERVOTES cursor for -- NOTE: convert to DATE truncates the Time-part!!! select convert(DATE, tstamp) as tdate, tstamp, action, rep from ( --Approved suggested edits. select se.ApprovalDate as tstamp, 'edit' as action, 2 as rep from SuggestedEdits se where se.OwnerUserId = @userid and se.ApprovalDate is not null and se.ApprovalDate >= @nexus UNION ALL -- Up and Down votes on Q's and A's select v.CreationDate as tstamp, 'invote' as action, 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 = 8 then v.BountyAmount else 0 end as rep from Posts p, Votes v where v.PostId = p.Id and p.OwnerUserId = @userid and p.PostTypeId in (1,2) and v.VoteTypeId in (1,2,3,8) and v.CreationDate > @nexus UNION ALL -- Bounties that were offered.... select v.CreationDate as tstamp, 'bounty' as action, -1 * v.BountyAmount as rep from Votes v where v.VoteTypeId = 9 and v.UserId = @userid and v.CreationDate > @Nexus ) as tdata order by tstamp open USERVOTES fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep while @@FETCH_STATUS = 0 begin if @pdate <> @rdate and @rcnt > 0 begin -- break point, new day, save old day's data select @cumulative = @cumulative + @rollingrep + @rollingbonus insert into #repday values (@username, @pdate, @pdate, @rollingrep, @rollingbonus, @rollingcap, @cumulative) -- reset our accumulators select @rollingrep = 0, @rollingcap = 0, @rollingbonus = 0, @pdate = @rdate end select @rcnt = @rcnt + 1 if @rrep > 10 or @raction = 'bounty' -- things that score > 10 (accept) are not capped - assume bonus never <= 10 select @rollingbonus = @rollingbonus + @rrep else -- things that score 10 or less are subject to cap select @rollingrep = @rollingrep + @rrep --if @rollingbonus < 0 --begin -- mo --end if @rollingrep > 200 begin -- last action passed the cap... but how much past? select @rtmp = @rollingrep - 200 -- set the value back to 200, and add the difference to the lost rep select @rollingrep = 200, @rollingcap = @rollingcap + @rtmp select @message = 'Vote Maxed ' + Convert(Varchar(20), @rdate) + ' ' + @raction + ' rep ' + Convert(varchar(3), @rrep) + ' ' + Convert(varchar(3), @rtmp) + ' rep wasted' print @message end fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep end close USERVOTES deallocate USERVOTES -- save away the last day's values that were not break-processed select @cumulative = @cumulative + @rollingrep + @rollingbonus insert into #repday values (@username, @rdate, @rdate, @rollingrep, @rollingbonus, @rollingcap, @cumulative) select @message = 'Replayed user ' + @username + ' with rep ' + Convert(varchar(10), @cumulative) + ' and repcnt ' + Convert(varchar(10), @rcnt) print @message fetch next from TOPUSERS into @userid, @username END close TOPUSERS deallocate TOPUSERS ; with UserReputation as ( select RepDate as RepDate, UserName as Name, Cumulative as Reputation from #REPDAY ) select * from UserReputation pivot (sum(Reputation) for Name in ([rolfl], [Jamal] , [ Blue ], [ Brown ])) as Pivoted order by RepDate ASC