The Top-10 Board
The public StreamVerse "Top Shows" board puts a position number next to every title. Editorial's rule is that two titles on identical watch time must show the same position — declaring one of them fourth would be a claim the data does not support — and the position after a shared one is skipped, so a board can read 1, 2, 3, 3, 5.
watch_events — one row per watch session: one sitting in which one subscriber watched one show. subscriber_id points at a row in subscribers, show_id at a row in shows, and watch_minutes is how long that sitting lasted. The same person can appear many times.
| id | subscriber_id | show_id | watch_minutes | watch_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 45 | 2023-05-01 |
| 2 | 1 | 5 | 60 | 2023-05-03 |
| 3 | 2 | 3 | 20 | 2023-05-02 |
| 4 | 3 | 2 | 55 | 2023-05-05 |
| 5 | 4 | 1 | 40 | 2023-05-06 |
| 6 | 4 | 4 | 70 | 2023-05-08 |
| 7 | 1 | 3 | 25 | 2023-05-10 |
| 8 | 3 | 5 | 50 | 2023-05-11 |
| 9 | 2 | 2 | 30 | 2023-05-12 |
| 10 | 5 | 4 | 65 | 2023-05-14 |
shows — one row per title in the catalogue. genre is a single label such as sci-fi or drama; release_year is the year the title came out.
| id | title | genre | release_year |
|---|---|---|---|
| 1 | Nebula Drift | sci-fi | 2021 |
| 2 | The Long Kitchen | drama | 2019 |
| 3 | Byte Size | comedy | 2022 |
| 4 | Deep Trench | documentary | 2020 |
| 5 | Neon Alley | sci-fi | 2023 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns three columns — title, minutes and show_rank — one row per show watched at least once. minutes is the show's total watch_minutes; show_rank is 1 for the most-watched show, and titles on identical minutes share a position while the next position is skipped. Return the rows lowest show_rank first, with titles sharing a position separated alphabetically by title.
Example output
Shape only — these titles and figures are invented. Two are level, so they share position 3 and position 4 goes unused:
| title | minutes | show_rank |
|---|---|---|
| Cold Harvest | 1320 | 1 |
| Glass Canyon | 980 | 2 |
| Harbour Lights | 640 | 3 |
| Paper Moon | 640 | 3 |
| Slow Orbit | 210 | 5 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in