Catalog Depth
Content strategy at StreamVerse is deciding what to license next, and the first slide is always the same: how deep is the shelf in each genre? A genre with one lonely title is a gap; a genre with many is a shelf the money already went to.
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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row per genre, with columns genre and n_shows, n_shows being how many titles carry that genre. Deepest shelf first; genres holding the same number of titles come back in alphabetical order of genre.
Example output
Shape only — these genres and figures are invented:
| genre | n_shows |
|---|---|
| thriller | 12 |
| horror | 5 |
| romance | 5 |
| western | 2 |
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