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 linguists, teachers and students of the Korean language

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 @rollingbequest 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,
    Accepts int not null,
    BountyDown int not null,
    BountyUp 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 @rollingbequest = 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,
                      @rollingbequest, @rollingbounty, @rollingcap)
            -- reset our accumulators
            select @rollingrep = 0,
                   @rollingcap = 0,
                   @rollinguncapped = 0,
                   @rollingbequest = 0,
                   @rollingbounty = 0,
                   @pdate = @rdate
        end
        
        select @rcnt = @rcnt + 1
        
        if @rrep = 15
            -- accepts are not capped
            select @rollinguncapped = @rollinguncapped + @rrep
        else if @raction = 'bounty' and @rrep >= 0
            select @rollingbounty = @rollingbounty + @rrep
        else if @raction = 'bounty'
            select @rollingrep = @rollingrep + @rrep,
                   @rollingbequest = @rollingbequest + @rrep
        else
            -- things that score 10 or less are subject to cap
            select @rollingrep = @rollingrep + @rrep
                    
        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,
               @rollingbequest, @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 [Regular],
       Accepts as [Accepts],
       BountyDown as [Bequest],
       BountyUp as [Bounty],
       CapLimited + Accepts + BountyUp 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 + Accepts + BountyUp >= 200 -- days which count for Mortarboard
        or Discarded > 0)               -- days with lost rep
   
order by Reputation DESC, RepDate ASC

fork query

edited may 27 14

created jan 31 14 rolfl