Exercise: RANK, DENSE_RANK, and Distribution Functions

Write a query that ranks all 10 rows in tracks by duration_seconds descending using RANK(). Return title, duration_seconds, and the rank as rnk. Order by rnk.

Write a query that uses DENSE_RANK() to rank each row in streams by duration_listened descending. Return stream_id, duration_listened, and the rank as dense_rnk. Order by dense_rnk, then stream_id.

Write a query that uses NTILE(4) over tracks ordered by duration_seconds ascending to assign each track to a quartile. Return title, duration_seconds, and the quartile number as quartile. Order by duration_seconds.

Write a query that returns each track's relative position when ordered by duration_seconds ascending using PERCENT_RANK(). Return title, duration_seconds, and pct_rank rounded to 2 decimals. Order by duration_seconds.

Write a query that uses DENSE_RANK() to return all tracks tied for the second-longest distinct duration_seconds. Return title and duration_seconds.

Write a query that ranks tracks by duration_seconds descending within each genre_id using RANK(). Join tracks to albums to access genre_id. Return genre_id, title, duration_seconds, and genre_rnk. Order by genre_id, then genre_rnk.

Write a query that uses DENSE_RANK() to return every track whose play count is among the top 2 distinct play counts. Return title and play_count. Order by play_count descending, then title.

Write a query that uses CUME_DIST() over streams ordered by duration_listened ascending to return the bottom 25% of streams. Return stream_id and duration_listened. Order by duration_listened, then stream_id.