7

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.

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

view current version

edited feb 1 14

created feb 1 14 rolfl