What's Our Most-Watched Show?
A licence is up for renewal at StreamVerse, and the negotiating team wants to walk in knowing which programme in the catalogue is genuinely holding the audience. The measure they have settled on is total time watched — not how many people started it, which flatters anything with a good thumbnail, but how many minutes it actually kept.
The viewing log is one row per session, so a programme's total is spread across as many rows as it has had viewings, by as many different subscribers. And the log records only a show_id; the title the negotiators need is in the catalogue.
shows — one row per programme. release_year is when it first aired.
| 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 |
watch_events — one row per viewing session. show_id points at a row in shows; watch_minutes is how long that session lasted.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single row with two columns, title and total_minutes, for the programme with the most minutes watched across every subscriber. Sessions from different people are added together into one figure per programme. Exactly one row comes back. No two StreamVerse programmes tie at the top on this data, so there is no tie to settle.
Example output — shape only, on an invented programme and total.
| title | total_minutes |
|---|---|
| Copper Sky | 420 |
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