Report on all days in which a user loses reputation, or earns 200 rep or more. If you specify -1 as the user, then it reports for all users on the whole site who have ever earned the mortarboard badge.
Q&A for project managers
declare @userid as int = ##UserId:int?-1## declare @userrep as int declare @epoch as date = '1 jan 2099' declare @pdate as date declare @rdate as date declare @rtime as datetime declare @raction as varchar(10) declare @rrep as int declare @rcnt as int declare @rtmp as int declare @message as varchar(250) declare @rollingrep as int declare @rollingbounty as int declare @rollingbequest as int declare @rollinguncapped as int declare @rollingcap as int ; create table #REPDAY ( UserId int not null, Reputation int not null, RepDate DATE not null, CapLimited int not null, Accepts int not null, BountyDown int not null, BountyUp int not null, Discarded int not null) ; -- loop through all users who have earned mortarboard -- which is an appoximate list of people who have lost rep. -- or, if a userid is specified, use that user. declare TOPUSERS cursor for select Users.Id as UserId, Users.Reputation as Reputation from Users, Badges where @userid < 0 and badges.Name = 'Mortarboard' and Badges.UserId = Users.id UNION ALL select Id, Reputation from Users where Id = @userid open TOPUSERS fetch next from TOPUSERS into @userid, @userrep while @@FETCH_STATUS = 0 begin -- for each user, reset the accumulators select @pdate = @epoch -- something different select @rcnt = 0 select @rollingrep = 0 select @rollingbequest = 0 select @rollingbounty = 0 select @rollinguncapped = 0 select @rollingcap = 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 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 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) UNION ALL -- Bounties received select v.CreationDate as tstamp, 'bounty' as action, v.BountyAmount from Posts p, Votes v where v.PostId = p.Id and p.OwnerUserId = @userid and p.PostTypeId = 2 and v.VoteTypeId = 9 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 = 8 and v.UserId = @userid ) 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 insert into #repday values (@userid, @userrep, @pdate, @rollingrep, @rollinguncapped, @rollingbequest, @rollingbounty, @rollingcap) -- reset our accumulators select @rollingrep = 0, @rollingcap = 0, @rollinguncapped = 0, @rollingbequest = 0, @rollingbounty = 0, @pdate = @rdate end select @rcnt = @rcnt + 1 if @rrep = 15 -- accepts are not capped select @rollinguncapped = @rollinguncapped + @rrep else if @raction = 'bounty' and @rrep >= 0 select @rollingbounty = @rollingbounty + @rrep else if @raction = 'bounty' select @rollingrep = @rollingrep + @rrep, @rollingbequest = @rollingbequest + @rrep else -- things that score 10 or less are subject to cap select @rollingrep = @rollingrep + @rrep 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 insert into #repday values (@userid, @userrep, @rdate, @rollingrep, @rollinguncapped, @rollingbequest, @rollingbounty, @rollingcap) select @message = 'Replayed user ' + Convert(varchar(10), @userid) + ' with rep ' + Convert(varchar(10), @userrep) + ' and repcnt ' + Convert(varchar(10), @rcnt) print @message fetch next from TOPUSERS into @userid, @userrep END close TOPUSERS deallocate TOPUSERS ; with UserLost as ( select UserId as LostId, sum(Discarded) as LostAmt from #REPDAY group by UserId ) select UserId as [User Link], Reputation as [Current Rep], Convert(Date, RepDate) as [Date], CapLimited as [Regular], Accepts as [Accepts], BountyDown as [Bequest], BountyUp as [Bounty], CapLimited + Accepts + BountyUp as [Day Rep], Discarded as [Lost Rep], LostAmt as [Total Lost], Convert(Decimal(8,2), 100.0 * (convert(real,LostAmt) / convert(real,Reputation + LostAmt)) ) as [Lost%] from #REPDAY, UserLost where LostId = UserId and ( CapLimited + Accepts + BountyUp >= 200 -- days which count for Mortarboard or Discarded > 0) -- days with lost rep order by Reputation DESC, RepDate ASC