## Reputation Races

7

(click on this box to dismiss)

Calculate a number of user's reputation growth over a specified period of time, plot the graphs.

Q&A for administrators, end users, developers and designers for ExpressionEngine® CMS

``````declare @days as int = ##Days:int?90##
declare @userorange as nvarchar(40) = ##UserOrange:string##
declare @usercyan as nvarchar(40) = ##UserCyan:string?-##
declare @userred as nvarchar(40) = ##UserRed:string?-##
declare @usergreen as nvarchar(40) = ##UserGreen:string?-##
declare @userpurple as nvarchar(40) = ##UserPurple:string?-##

declare @nexus as date = dateadd(day, - @days, CURRENT_TIMESTAMP)

declare @userid as int
declare @userrep as int
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

declare @pivot as NVARCHAR(MAX)
declare @query as NVARCHAR(MAX)

create table #REPDAY (
RepDate date not null,
RepWeek date not null,
UserRep int not null,
CapLimited int not null,
UnCapped int not null,
Cumulative int not null)
;

select u.Id as UserId,
u.DisplayName as Name,
u.Reputation as Reputation
into #Users
from Users u
where u.DisplayName in (@userorange, @usercyan, @userred, @usergreen, @userpurple)
-- and not u.DisplayName in ('Cyan', 'Red', 'Green', 'Purple')
and not exists (
select s.Id
from Users s
where s.Id != u.Id
and s.Reputation > u.Reputation
and s.DisplayName = u.DisplayName)
;

create table #DATES (
EventDate Date not null)

select @pdate = Convert(date, current_timestamp);

while @pdate > @epoch
begin
select @pdate = DateAdd(dd, -1, @pdate);
insert into #DATES values (@pdate);
end

declare TOPUSERS cursor for
select * from #Users

open TOPUSERS

fetch next from TOPUSERS into @userid, @username, @userrep
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

-- 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 = 9 then IsNull(v.BountyAmount, 0)
else 0
end as rep
where v.PostId = p.Id
and p.OwnerUserId = @userid
and p.PostTypeId in (1,2)
and v.VoteTypeId in (1,2,3,9)
and v.CreationDate > @nexus

UNION ALL

-- Bounties that were offered....
select v.CreationDate as tstamp, 'bounty' as action, -1 * v.BountyAmount as rep
where v.VoteTypeId = 8
and v.UserId = @userid
and v.CreationDate > @Nexus

UNION ALL

-- Blank date events
select EventDate as tstamp, 'dummy' as action, 0 as rep
from #DATES

) as tdata
order by tstamp

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, @userrep, @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

-- save away the last day's values that were not break-processed
select @cumulative = @cumulative + @rollingrep + @rollingbonus
insert into #repday
values (@username, @rdate, @rdate, @userrep, @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, @userrep
END

close TOPUSERS
deallocate TOPUSERS

;

SET @pivot = STUFF((SELECT ',' + QUOTENAME(Name)
FROM #USERS
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
;

print @pivot
;

from #REPDAY out
where not exists (
select 1
from #REPDAY sub
and sub.RepDate > out.RepDate)
), UserReputation as (
select rd.RepDate as RepDate,
from #REPDAY rd,

)
select *
into #results
from UserReputation

;
set @query = 'select * from #results pivot (sum(Reputation) for Name in (' + @pivot + ')) as Pivoted  order by RepDate ASC'
;
execute(@query)``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached