Exercise: Top-N Per Group
Last Updated: May 12, 2026
For this exercise, we will work with below data:
Exercise 1
Write a query that returns the first track (by track_number) of each album. Show album and track. Order by album.
Expected Query
Expected Output
Exercise 2
Write a query that returns each artist's longest track. Show artist, track, and duration_seconds. If an artist has multiple tracks tied at the same maximum duration, return the one with the lowest track_number. Use ROW_NUMBER partitioned by artists.artist_id. Order the result by artist.
Expected Query
Expected Output
Exercise 3
Write a query that returns the top 2 most-streamed tracks per genre using ROW_NUMBER. Break ties by track.title ascending. Show genre, track, and stream_count. Order by genre, then stream_count descending, then track.
Expected Query
Expected Output
Exercise 4
Write a query that returns the top 3 longest tracks per genre by duration_seconds. Use ROW_NUMBER with t.title as the tiebreaker. Show genre, track, and duration_seconds. Order by genre, then duration_seconds descending.
Expected Query
Expected Output
Exercise 5
Write a query that returns the user with the highest total duration_listened per country (from users.country), including all users tied at the top. Use RANK. Show country, username, and total_listened. Order by country, then username.