Watch Hours by Genre — SQL Joins Problem
Content acquisition decides next year's budget by genre. Using every watch session, compute the total time watched per genre of the title watched, in hours.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #475
Problem statement
Content acquisition decides next year's budget by genre. Using every watch session, compute the total time watched per genre of the title watched, in hours.
Return the columns genre and watch_hours, where watch_hours is the sum of watched_minutes divided by 60 and rounded to 2 decimal places. Genres with no sessions do not appear. Order the rows by watch_hours from highest to lowest, and by genre alphabetically when two genres tie.
Tables
Table: Title
| Column | Type |
|---|---|
| title_id | int |
| title_name | varchar |
| genre | varchar |
Primary key: title_id.
One row per title; every title has exactly one primary genre.
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| title_id | int |
| watched_minutes | int |
Primary key: session_id.
One row per playback session; title_id always names a row of Title.
Examples
Example 1
Title
| title_id | title_name | genre |
|---|---|---|
| 1 | Coastal Cops | Thriller |
| 2 | Backbenchers | Comedy |
| 3 | Silk Road Stories | Documentary |
| 4 | Gully Kings | Comedy |
| 5 | Desert Rose | Romance |
WatchSession
| session_id | profile_id | title_id | watched_minutes |
|---|---|---|---|
| 1 | 1 | 1 | 50 |
| 2 | 2 | 2 | 25 |
| 3 | 1 | 4 | 45 |
| 4 | 3 | 1 | 20 |
| 5 | 3 | 3 | 70 |
| 6 | 2 | 2 | 100 |
Output
| genre | watch_hours |
|---|---|
| Comedy | 2.83 |
| Documentary | 1.17 |
| Thriller | 1.17 |
How to solve Watch Hours by Genre
The genre lives on Title and the minutes on WatchSession, so the query joins the two on title_id and then aggregates: GROUP BY t.genre and SUM(w.watched_minutes). An inner join is right here because the statement leaves out genres nobody watched.
Convert to hours after summing — SUM(minutes) / 60 — and round once. Rounding each session's hours first and adding the rounded pieces can drift by a few hundredths from the true total. / is decimal division in MySQL, so 45 minutes become 0.75 hours, not 0.
The order has two keys: the hours descending, then the genre name ascending to settle equal totals. Sorting by the raw minute total gives the same order, since two genres with the same rounded hours have the same minutes here (minutes are whole numbers). The plan is one pass over the sessions plus one primary-key lookup each.
Reference solution (MySQL)
SELECT t.genre, ROUND(SUM(w.watched_minutes) / 60, 2) AS watch_hours
FROM WatchSession w
JOIN Title t ON t.title_id = w.title_id
GROUP BY t.genre
ORDER BY watch_hours DESC, t.genreAnother way
SELECT genre, ROUND(total / 60, 2) AS watch_hours
FROM (SELECT t.genre, SUM(w.watched_minutes) AS total FROM Title t JOIN WatchSession w ON w.title_id = t.title_id GROUP BY t.genre) g
ORDER BY total DESC, genre← Profiles With No Streams Since the New Year · Accounts With Two or More Kids Profiles →