Hotstar: Next-Day Views of Regional Serials — SQL Problem
Regional TV serials air on the broadcast channel in the evening and land on Hotstar the same night; the content team tracks how many people catch up the day…
- Difficulty: Easy
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #702
Problem statement
Regional TV serials air on the broadcast channel in the evening and land on Hotstar the same night; the content team tracks how many people catch up the day after an episode aired, a sign the show is pulling viewers back to the app.
For every serial in Episode, return serial_name and next_day_views: the number of views of its episodes made on the calendar day right after that episode's aired_on date. Views on the airing day itself or two or more days later do not count. A serial with no next-day views is listed with 0. Order by serial_name.
A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.
Tables
Table: Episode
| Column | Type |
|---|---|
| episode_id | int |
| serial_name | varchar |
| language | varchar |
| aired_on | date |
Primary key: episode_id.
One row per episode. A serial has many episodes; serial_name identifies the serial.
Table: EpisodeView
| Column | Type |
|---|---|
| view_id | int |
| episode_id | int |
| profile_id | int |
| viewed_on | date |
Primary key: view_id.
One row per play of an episode on the app. A profile can view the same episode more than once; each play counts.
Examples
Example 1
Episode
| episode_id | serial_name | language | aired_on |
|---|---|---|---|
| 1 | Pyaar Ki Patang | Hindi | 2025-03-03 |
| 2 | Pyaar Ki Patang | Hindi | 2025-03-04 |
| 3 | Neer Kolam | Tamil | 2025-03-03 |
| 4 | Gharkul Diaries | Marathi | 2025-02-28 |
EpisodeView
| view_id | episode_id | profile_id | viewed_on |
|---|---|---|---|
| 1 | 1 | 501 | 2025-03-04 |
| 2 | 1 | 502 | 2025-03-03 |
| 3 | 2 | 501 | 2025-03-05 |
| 4 | 2 | 503 | 2025-03-07 |
| 5 | 3 | 504 | 2025-03-05 |
| 6 | 1 | 503 | 2025-03-04 |
| 7 | 4 | 505 | 2025-03-01 |
Output
| serial_name | next_day_views |
|---|---|
| Gharkul Diaries | 1 |
| Neer Kolam | 0 |
| Pyaar Ki Patang | 3 |
How to solve Hotstar: Next-Day Views of Regional Serials
A view counts when its date is exactly one day after its episode's airing date. The comparison needs both rows side by side, so the query joins EpisodeView to Episode and tests viewed_on = DATE_ADD(aired_on, INTERVAL 1 DAY). Date arithmetic rather than "day number + 1" matters at a month boundary: an episode aired on 28 February is caught up on 1 March.
Every serial must appear, even with no next-day views. A LEFT JOIN keeps each episode, and putting the date test in the ON clause attaches only qualifying views — episodes with none get a NULL view. COUNT(v.view_id) counts non-NULL views, so those serials come out as 0. Putting the date test in WHERE instead would delete the NULL rows and lose those serials.
The alternative joins every view and counts conditionally with DATEDIFF(...) = 1 inside a SUM(CASE …); unmatched episodes contribute a NULL DATEDIFF, which falls to the ELSE 0. Results are grouped by serial name and ordered by it. One pass over episodes with an index lookup into views.
Reference solution (MySQL)
SELECT e.serial_name, COUNT(v.view_id) AS next_day_views
FROM Episode e
LEFT JOIN EpisodeView v
ON v.episode_id = e.episode_id
AND v.viewed_on = DATE_ADD(e.aired_on, INTERVAL 1 DAY)
GROUP BY e.serial_name
ORDER BY e.serial_nameAnother way
SELECT e.serial_name, SUM(CASE WHEN DATEDIFF(v.viewed_on, e.aired_on) = 1 THEN 1 ELSE 0 END) AS next_day_views
FROM Episode e LEFT JOIN EpisodeView v ON v.episode_id = e.episode_id
GROUP BY e.serial_name
ORDER BY e.serial_name← Hotstar: Buffering Share of Watch Time by CDN · Hotstar: Watchlist Titles the Profile Never Played →