The Genre Explorers
New-release emails at StreamVerse work on people with broad taste and irritate everyone else, so marketing wants the subscribers who have watched titles from more than one genre, with a count of how wide their taste actually runs. Two sittings on two sci-fi shows is still one genre.
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 |
subscribers — one row per person who pays for StreamVerse. plan is one of basic, standard or premium; country is a two-letter market code; signup_date is the day they signed up.
| id | name | plan | signup_date | country |
|---|---|---|---|---|
| 1 | Ana Torres | premium | 2022-11-01 | US |
| 2 | Ben Osei | basic | 2023-01-15 | UK |
| 3 | Chloe Martin | standard | 2023-02-20 | US |
| 4 | Dev Malhotra | premium | 2023-03-10 | IN |
| 5 | Ella Novak | basic | 2023-04-05 | UK |
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 |
All three tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns two columns, name and genres, listing only subscribers who have watched titles from two or more different genres. genres is how many different genres that subscriber has watched. Widest taste first; two subscribers on the same number come back in alphabetical order of name.
Example output
Shape only — these subscribers and figures are invented:
| name | genres |
|---|---|
| Femi Adeyemi | 4 |
| Hana Kito | 3 |
| Ingrid Sol | 2 |
| Jonas Peak | 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