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.

Android Enthusiasts


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)

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

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 ([##UserRed##], [##UserGreen##] , [##UserBlue##], [##UserCyan##], [##UserBrown##])) as Pivoted   
order by RepDate ASC

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results