Reputation Races


Please login or register to vote for this query.

(click on this box to dismiss)

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

Genealogy and Family History Meta

Q&A about the site for expert genealogists and people interested in genealogy or family history

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

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

create table #USERS (
    UserId int not null,
    Name NVarchar(40) not null,
    Reputation int not null
create table #REPDAY (
    UserName nvarchar(40),
    RepDate date not null,
    RepWeek date not null,
    UserRep int not null,
    CapLimited int not null,
    UnCapped int not null,
    Discarded int not null,
    Cumulative int not null)
create table #results (
    RepDate DATE,
    Name NVarchar(50),
    Reputation int

create table #DATES (
    EventDate Date not null)

select @pdate = DateAdd(dd, 1 - DatePart(dw, current_timestamp), Convert(date, current_timestamp));

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

insert into #USERS
    select u.Id as UserId,
           '''' + u.DisplayName + '''' as Name,
           u.Reputation as Reputation
    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)

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

print @pivot

declare TOPUSERS cursor for select * from #USERS


fetch next from TOPUSERS into @userid, @username, @userrep
while @@FETCH_STATUS = 0
    -- 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 = 9 then IsNull(v.BountyAmount, 0)
                       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,9)
              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 = 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
    open USERVOTES
    fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep
    while @@FETCH_STATUS = 0
        if @pdate <> @rdate and @rcnt > 0
            -- 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
        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
            -- things that score 10 or less are subject to cap
            select @rollingrep = @rollingrep + @rrep
        --if @rollingbonus < 0
        --    mo
        if @rollingrep > 200
            -- 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
        fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep
    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, @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

deallocate TOPUSERS


with RepAdjust as (
    select UserName as UserName,
           UserRep - Cumulative as Adjust
    from #REPDAY out
    where not exists (
        select 1
        from #REPDAY sub
        where sub.UserName = out.UserName
          and sub.RepDate > out.RepDate)
), UserReputation as (
       select rd.RepDate as RepDate,
              rd.UserName as Name,
              rd.Cumulative + ra.Adjust as Reputation
       from #REPDAY rd,
            RepAdjust ra
       where ra.UserName = rd.UserName
insert into #results
select *
from UserReputation

--select * from #results

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


Enter Parameters

Switch to main site
loading Hold tight while we fetch your results