top ranked rep-earning days, out of all users

0

Please login or register to vote for this query.

(click on this box to dismiss)

only takes votes into account, and doesn't apply the daily cap.

Stack Overflow

Q&A for professional and enthusiast programmers

set nocount on set ansi_warnings off


declare @EndDate date = (select min(create_date) from sys.tables)
declare @StartDate date = dateadd(d,-7*##Weeksback:int?2##, @enddate) 

select 
postid,
  p.owneruserid as [Userid],
  cast(v.creationdate as date) as [Date],
  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 ) 
  +
  sum(case 
    when v.votetypeid=1 then
      case when p.posttypeid=2 then 15 end             --Accepted Answer
  end ) as [rep]
------into #rep 
from posts p left outer join votes v on v.postid = p.id 
where               --p.owneruserid=##UserID## and     ----<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  v.votetypeid in (1,2,3)                              --Accepted, Upvote, Downvote only
  and p.posttypeid in (1,2)                            --Q or A only
  and userid =  88656 and year(v.creationdate)=2011 and month(v.creationdate)=6
group by p.owneruserid,cast(v.creationdate as date) ,postid
/*


select 
  rank() over (partition by [date] order by [rep] desc) as Rank,
  rank() over (order by [rep] desc) as RankOverall,
  userid,
  [date],
  [rep]
from #rep
where rep is not null and userid is not null

Enter Parameters

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