SITE COMBINED REPUTATION earned per day

1

Please login or register to vote for this query.

(click on this box to dismiss)

Rep earned from questions & answers per day, total for all users. Does not account for bounty and certain types of earned rep, and does not account for the daily cap, but this is nonetheless within 2% of actual daily rep.

Chess

Q&A for serious players and enthusiasts of chess

set nocount on; set ansi_warnings off;

declare @StartDate date= (select min(creationdate) from posts) 
declare @EndDate date=(select min(create_date) from sys.tables)

--=-=-=-=-=-=-=-=-=--> CALCULATE REP FROM VOTES <--=-=-=-=-=-=-=-=-=--
select 
 v.creationdate,
  sum(case 
    when v.votetypeid=2 then
      case when p.posttypeid=1 then 5                  --Upvote Question
           when p.posttypeid=2 then 10 end             --Upvote Answer
    when v.votetypeid=3 then -2                        --Downvote Q or A
  end ) as [rep],
  sum(case 
    when v.votetypeid=1 then
      case when p.posttypeid=2 then 15 end   --Accepted Answer
  end ) as [accepted]
into #rep from posts p left outer join votes v on v.postid = p.id 
where -- ( v.creationdate between '2018-1-7' and '2018-1-14' or v.creationdate between '2018-9-1' and '2018-9-2' ) and
    v.votetypeid in (1,2,3)                              --Accepted, Upvote, Downvote only
  and p.posttypeid in (1,2)                            --Q or A only
group by v.creationdate

--=-=-=-=-=--> Create running count of users per day (the fast way)<--=-=-=-=-=-=--
select cast(creationdate as date) as [ord], count(*) as [total]
into #usersPerDay from users group by cast(creationdate as date) 

DECLARE @TotalTable table(ord date, total int, running_total int)
DECLARE forward_cursor CURSOR FAST_FORWARD 
FOR SELECT ord, total FROM #usersPerDay ORDER BY ord
OPEN forward_cursor 
DECLARE @running_total int, 
    @ord date, 
    @total int
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @ord, @total 
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @total
     INSERT @TotalTable VALUES(@ord, @total, @running_total)
     FETCH NEXT FROM forward_cursor INTO @ord, @total 
END
CLOSE forward_cursor
DEALLOCATE forward_cursor


-----------------------------

select
  r.creationdate,
  [rep]+[accepted] as [rep earned on this day],
    running_total as [total # of users to date]
from @TotalTable t left join #rep r on t.ord=r.creationdate
--group by r.creationdate
order by r.creationdate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results