Which Shows Are Punching Above Their Weight?
Content acquisition wants to renew licenses for shows outperforming the catalog average in total watch time.
watch_events
| 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
| 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 |
Task: Write a query that returns title and total_minutes for every show whose lifetime watched minutes are strictly above the average show. The bar to clear is the mean of the per-show lifetime totals — not the mean of individual viewing sessions, which is a different and much smaller number. A show nobody has watched has no total, and takes no part in either the answer or the average. Hand the renewal candidates back most-watched first, two shows on the same total separated by title, alphabetically.
Example output — shape only, on invented shows and totals.
| title | total_minutes |
|---|---|
| Copper Sky | 260 |
| Winter Signal | 185 |
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