A rank function assigns ordinal positions to rows according to an ordering. RANK() is a window function: it orders results while retaining each row, and tied rows receive the same rank. section 3.5
Ranking songs by total play duration
Aggregate plays by song, then rank the totals from largest to smallest: SELECT song_id, SUM(play_duration) AS total_play_duration, RANK() OVER (ORDER BY SUM(play_duration) DESC) AS song_rank FROM streaming.plays GROUP BY song_id ORDER BY song_rank; The output ranks song 4 first with 1, song 1 second with 2, and song 5 tenth with 10. section 3.5
Listing 3.14 Ranking songs by total play duration
SELECT song_id, SUM(play_duration) AS total_play_duration, RANK() OVER (ORDER BY SUM(play_duration) DESC) AS song_rank FROM streaming.plays GROUP BY song_id ORDER BY song_rank;
Inspect the complete SQL listing for ranking songs by total play duration. section 3.5
