Each Reader's Favourite Genre
Problem
An e-book platform logs every time a reader opens a book. For each reader, find the genre(s) they opened most often. If two genres tie for the top spot for a reader, return all of them.
`Readers`:
- `reader_id` (int), `display_name` (varchar)
`Reads`:
- `read_id` (int), `read_on` (date), `reader_id` (int), `genre_id` (int)
`Genres`:
- `genre_id` (int), `genre_name` (varchar)
Return `reader_id`, `genre_id`, and `genre_name` for every reader's most-opened genre(s). Rows may be in any order.
Tables
Example rows — the live problem includes the full dataset.
| reader_id | display_name |
|---|---|
| 1 | Nora |
| 2 | Sven |
| 3 | Yuki |
| read_id | read_on | reader_id | genre_id |
|---|---|---|---|
| 1 | 2022-07-31 | 1 | 11 |
| 2 | 2022-07-30 | 2 | 12 |
| 3 | 2022-08-29 | 3 | 13 |
| genre_id | genre_name |
|---|---|
| 11 | Mystery |
| 12 | Romance |
| 13 | SciFi |
Expected output
Your answer should return 6 rows with the columns reader_id, genre_id, genre_name.
Starter code (SQL)
SELECT *
FROM Readers;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock