0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

--Show the top 3 users (Id) for each month (January-December), 
--by total ViewCount (for all user posts). Include DisplayName and each user’s ranking within the month

with cte1 as (
select 
month(p.CreationDate) mm,
u.id,
u.DisplayName name,
sum(ViewCount) total,
rank()over(partition by month(p.CreationDate) order by sum(ViewCount) desc) rnk

from users u
join Posts p
on u.id=p.OwnerUserId
group by month(p.CreationDate),u.id,u.DisplayName )

select distinct 
mm,
name,
rnk
from 
cte1 
where rnk<=3

fork query

edited sep 6

created sep 6 anonymous