Which Genre Keeps People Watching Longest?
StreamVerse commissions for the year ahead next month, and content strategy has been asked which kinds of programme hold an audience once it has started watching. Not which are most popular — that is a different report — but which earn the longer sittings.
The viewing log knows how long each session ran, and it knows which programme was on. What it does not know is what kind of programme that was: genre is a property of the show, recorded once in the catalogue, and the log only carries a show_id. So the label has to be fetched before anything can be totted up by kind. Note too that one genre covers two different shows, and their sessions belong in the same figure.
shows — one row per programme in the catalogue. genre is the kind of programme it is.
| 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. watch_minutes is how long that session lasted; show_id points at a row in shows.
| 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 two columns, genre and avg_minutes, with one row for every genre that has at least one viewing session against it, rounded to 2 decimal places. avg_minutes is the average length of a single session, so a genre with two shows averages across all their sessions together rather than averaging the two shows' averages. A genre nobody has watched produces no row. Rows come back with the longest average first, ties settled on genre alphabetically.
Example output — shape only; the figures below are invented, not this data's answer.
| genre | avg_minutes |
|---|---|
| history | 81.5 |
| thriller | 55.25 |
| romance | 30 |
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