I have a table Movie with columns Movie and Viewer where each movie is viewed by any user any number of times, so the table can have multiple same entries. I want to find the Top N most viewed movies and then the Top K viewers for each of the Top N movies. How can I apply group by or partition by effectively in such scenario ? Or if there is any better approach to this, please share. Thanks!

So, in this table Batman is the most watched movie is Batman followed by X-Men so I want the result table to look like :

I understand that I can group by movie and then do order by count(*) desc but this doesn't give me the second column which is grouped by viewer and the count for each viewer also.

CodePudding user response：

Consider below approach (assuming Top 3 movies with Top 2 users)

select movie, user, view_count
from (
  select distinct *,  
    count(*) over(partition by movie) movie_views,
    count(*) over(partition by movie, user) view_count
  from your_table
)
qualify dense_rank() over(order by movie_views desc) <=3 
and row_number() over(partition by movie order by view_count desc) <=2
-- order by movie_views desc, view_count desc     

if applied to sample data in your question - output is