Hotstar: Subscriptions With No Play in Their First Week
A subscriber who pays and then does not watch anything in the first week is the strongest churn signal Hotstar's lifecycle team has, so it sends them a…
- Difficulty: Medium
- Topics: Joins, Dates, Subqueries
- Dialect: MySQL
- Problem: #712
Problem statement
A subscriber who pays and then does not watch anything in the first week is the strongest churn signal Hotstar's lifecycle team has, so it sends them a curated "start here" mail. A subscription's first week is its started_on day and the six days after it; plays before started_on (on an earlier subscription, say) do not count.
Return sub_id, user_id and plan for every subscription whose user has no play during that subscription's first week. Return the rows in any order.
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: Subscription
| Column | Type |
|---|---|
| sub_id | int |
| user_id | int |
| plan | enum(mobile, super, premium) |
| started_on | date |
Primary key: sub_id.
One row per paid subscription. A user who lapsed and came back has more than one.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| user_id | int |
| played_at | datetime |
Primary key: play_id.
One row per play by a user, on any subscription.
Examples
Example 1
Subscription
| sub_id | user_id | plan | started_on |
|---|---|---|---|
| 1 | 41 | mobile | 2025-02-01 |
| 2 | 42 | super | 2025-02-03 |
| 3 | 43 | premium | 2025-02-05 |
| 4 | 41 | super | 2025-03-01 |
| 5 | 44 | mobile | 2025-02-10 |
Play
| play_id | user_id | played_at |
|---|---|---|
| 1 | 41 | 2025-02-07 23:50:00 |
| 2 | 42 | 2025-02-10 08:00:00 |
| 3 | 42 | 2025-02-02 21:00:00 |
| 4 | 43 | 2025-02-05 00:05:00 |
| 5 | 41 | 2025-03-09 20:00:00 |
Output
| sub_id | user_id | plan |
|---|---|---|
| 2 | 42 | super |
| 4 | 41 | super |
| 5 | 44 | mobile |
How to solve Hotstar: Subscriptions With No Play in Their First Week
Each subscription has its own window, [started_on, started_on + 7 days), and the question is whether its user has any play inside it — an anti join with a range condition. NOT EXISTS with played_at >= started_on AND played_at < DATE_ADD(started_on, INTERVAL 7 DAY) states it exactly. The upper bound is the start of the eighth day, written as < on a date: played_at <= '2025-02-07' would compare a datetime with midnight of the 7th and miss user 41's play at 23:50 that evening.
The window is per subscription, so a returning user is judged afresh: user 41 watched in February's first week but not in March's (the March play came on the ninth day), so subscription 4 is flagged while subscription 1 is not. A play before started_on, like user 42's on 2 February, belongs to no window.
The alternative attaches in-window plays with a LEFT JOIN (DATEDIFF(...) BETWEEN 0 AND 6, which compares calendar days) and keeps groups where none attached. With an index on Play(user_id, played_at) the NOT EXISTS form is a range probe per subscription.
Reference solution (MySQL)
SELECT s.sub_id, s.user_id, s.plan
FROM Subscription s
WHERE NOT EXISTS (
SELECT 1 FROM Play p
WHERE p.user_id = s.user_id
AND p.played_at >= s.started_on
AND p.played_at < DATE_ADD(s.started_on, INTERVAL 7 DAY)
)Another way
SELECT s.sub_id, s.user_id, s.plan
FROM Subscription s
LEFT JOIN Play p ON p.user_id = s.user_id AND DATEDIFF(p.played_at, s.started_on) BETWEEN 0 AND 6
GROUP BY s.sub_id, s.user_id, s.plan
HAVING COUNT(p.play_id) = 0← Hotstar: Accounts Streaming From Three Cities in a Day · Hotstar: Unsold Ad Slots by Innings Phase →