6

Please login or register to vote for this query.

(click on this box to dismiss)

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 those using, extending or developing Emacs

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 @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,
    UnCapped int not null,
    Bounty 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 @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, @rollingbounty, @rollingcap)
            -- reset our accumulators
            select @rollingrep = 0, @rollingcap = 0, @rollinguncapped = 0, @rollingbounty = 0, @pdate = @rdate
        end
        
        select @rcnt = @rcnt + 1
        
        if @rrep > 10 or (@raction = 'bounty' AND @rrep >= 0)
            -- things that score > 10 (accept) are not capped - assume bonus never <= 10
            select @rollinguncapped = @rollinguncapped + @rrep
        else
            -- things that score 10 or less are subject to cap
            select @rollingrep = @rollingrep + @rrep
            
        if @raction = 'bounty'
            select @rollingbounty = @rollingbounty + @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
    insert into #repday
       values (@userid, @userrep, @rdate, @rollingrep, @rollinguncapped, @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 [Actual Regular],
       UnCapped as [Accepts&Bounty],
       Bounty as [Bounty],
       CapLimited + UnCapped 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 + UnCapped >= 200 -- days which count for Mortarboard
        or Discarded > 0)               -- days with lost rep
   
order by Reputation DESC, RepDate ASC

view current version

edited may 27 14

created jan 31 14 rolfl