Hotstar: Subscriptions With No Play in Their First Week

Medium
712.

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 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

ColumnTypeValues
sub_idint
user_idint
planenummobile, super, premium
started_ondate

Primary key: sub_id.

One row per paid subscription. A user who lapsed and came back has more than one.

Table: Play

ColumnType
play_idint
user_idint
played_atdatetime

Primary key: play_id.

One row per play by a user, on any subscription.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Subscription

sub_iduser_idplanstarted_on
141mobile2025-02-01
242super2025-02-03
343premium2025-02-05
441super2025-03-01
544mobile2025-02-10

Play

play_iduser_idplayed_at
1412025-02-07 23:50:00
2422025-02-10 08:00:00
3422025-02-02 21:00:00
4432025-02-05 00:05:00
5412025-03-09 20:00:00

Output

sub_iduser_idplan
242super
441super
544mobile
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
sub_idint
user_idint
planenum(mobile, super, premium)
started_ondate

Primary key: sub_id.

One row per paid subscription. A user who lapsed and came back has more than one.

Table: Play

ColumnType
play_idint
user_idint
played_atdatetime

Primary key: play_id.

One row per play by a user, on any subscription.

Examples

Example 1

Subscription

sub_iduser_idplanstarted_on
141mobile2025-02-01
242super2025-02-03
343premium2025-02-05
441super2025-03-01
544mobile2025-02-10

Play

play_iduser_idplayed_at
1412025-02-07 23:50:00
2422025-02-10 08:00:00
3422025-02-02 21:00:00
4432025-02-05 00:05:00
5412025-03-09 20:00:00

Output

sub_iduser_idplan
242super
441super
544mobile

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 →