Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| content_id | integer | Content identifier. |
| genre | varchar | Genre name. |
| Column Name | Type | Description |
|---|---|---|
| content_id | integer | Watched content identifier. |
| hours_watched | decimal | Watch duration. |
Genre
Popularity
Find the most popular genre based on total watch time.
Table
Sample Data
Input
Output
Sample Input: content
| content_id | genre |
|---|---|
| 1 | Drama |
| 2 | Comedy |
| 3 | Drama |
| 4 | Action |
Sample Input: watching_activity
| content_id | hours_watched |
|---|---|
| 1 | 2.0 |
| 2 | 1.5 |
| 3 | 3.0 |
| 4 | 2.5 |
| 1 | 1.0 |
| 2 | 2.0 |
| 3 | 2.5 |
| 4 | 1.5 |
Expected Output
| genre | total_hours |
|---|---|
| Drama | 8.5 |
SQL Editor
Run
Query
Waiting for query
| genre | total_hours |
|---|---|
| Drama | 8.5 |
Hints
Unlock
Clues
Hint 01: Genre lives in content, hours live in watching_activity.
Hint 02: Join by content_id.
Hint 03: Group by genre and SUM(hours_watched).
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT c.genre, SUM(w.hours_watched) AS total_hours FROM watching_activity w JOIN content c ON w.content_id = c.content_id GROUP BY c.genre ORDER BY total_hours DESC LIMIT 1;
Explanation
Step By
Step
01
Attach each watch row to its content genre.
02
Sum watch time for every genre.
03
Sort totals descending and return the top genre.
Related Questions