#1 TOP COMMENTER per day (for whole sale, last 90 days)

1

Please login or register to vote for this query.

(click on this box to dismiss)

rep per user per tag per day? without timing out? Determine if you have been "the best at something for a day"

Moderators

Q&A for community managers, administrators, and moderators

declare @sedeUpdate date = dateadd(d,-1,( select min(create_date) from sys.tables ))

select 
  rank() over (partition by cast(creationdate as date) order by count(*) desc) as rank,
  rank() over (order by count(*) desc) as rankOverall,
  cast(creationdate as date) as [Date],
  UserId,
  count(*) as [Count]
into #commenters from comments
where userid is not null and creationdate between dateadd(d,-90,@sedeUpdate ) and @sedeUpdate 
group by cast(creationdate as date), userid
order by cast(creationdate as date),count(*) desc

select 
  userid as [User Link],
  format([date], 'yyyy-MM-dd') as [Top commenter on],
  [count] as [#Comments],
  rankOverall
from #commenters 
where rank =1
order by [date]

Enter Parameters

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