SQL: Get top records per category, per day, per country?

Question

A little trickier than just getting the top # per category. I want the top 2 videos per artist, per day per country.

My code, which didn't give me the right results is:

    Select * 
    From 
   (
    Select t2.*, dense_rank() over(partition by artist order by views desc)
    From
      (select country, day, artist, song, sum(view) as views
        From t1 
        Group by 1,2,3,4
        ) t2
    )
Where rn >=5 

Sample data results

| Country | Date | artist   | video | views | rn |
|---------|------|----------|-------|-------|----|
| US      | Jan1 | Beyonce  | ab    | 100   | 1  |
| US      | Jan1 | Beyonce  | ac    | 99    | 2  |
| US      | Jan2 | C. Brown | ad    | 89    | 1  |
| US      | Jan2 | C. Brown | ai    | 103   | 2  |
| AU      | Jan1 | Beyonce  | bf    | 99    | 1  |
| AU      | Jan1 | Beyonce  | bb    | 89    | 2  |

I want all artists per day, per country but only 10 videos per artist.. I am kind confused as to how to achieve this.. I generally struggle when it comes to window functions, so I would appreciate any help.

I am using Amazon Redshift

Thanks

Answer 1

You need to partition by all of the columns you mentioned since you are ranking views within each combination of these elements. Because you've renamed the aggregate column as "views", you need to call it by that name. Finally, if you want the top 2 videos/songs, use this condition: where rn <= 2

   Select * 
    From 
   (
    Select t2.*, dense_rank() over(partition by country, day, artist order by views desc)
    From
      (select country, day, artist, song, sum(views) as views
        From t1 
        Group by 1,2,3,4
        ) t2
    )
Where rn <= 2

Answer 2

This will rank per artist per day the views and show the two two for each artist per day

    Select * 
    From 
   (
    Select t2.*, ROW_NUMBER() over(partition by artist, day, country order by views desc) as rn
    From t1  t2
    )
Where rn <= 2