Hotstar: Subscribers Covered on Every Playoff Match Day

Hard
722.

Hotstar: Subscribers Covered on Every Playoff Match Day

For a loyalty reward, Hotstar's subscriptions team wants the users who had a paid pass running on every playoff match day. A user may hold several passes — back to back or overlapping — and a day counts as covered if any of the user's passes covers it (starts_on and ends_on are both inclusive). A refunded pass covers nothing.

Return the user_id of every user with at least one pass who was covered on every date in MatchDay. 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: MatchDay

ColumnType
match_datedate
fixturevarchar

Primary key: match_date.

One row per playoff match day; there is always at least one.

Table: SeasonPass

ColumnTypeValues
pass_idint
user_idint
planenummobile, super, premium
starts_ondate
ends_ondate
statusenumactive, refunded

Primary key: pass_id.

One row per pass bought; ends_on is on or after starts_on.

Example

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

MatchDay

match_datefixture
2025-05-20Qualifier 1
2025-05-21Eliminator
2025-05-23Qualifier 2
2025-05-25Final

SeasonPass

pass_iduser_idplanstarts_onends_onstatus
181super2025-05-012025-05-31active
282mobile2025-05-152025-05-21active
382mobile2025-05-222025-06-21active
483premium2025-05-202025-05-24active
583premium2025-05-242025-06-23refunded
684mobile2025-05-212025-06-20active
785super2025-05-102025-05-23active
885super2025-05-182025-05-25active

Output

user_id
81
82
85
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: Subscribers Covered on Every Playoff Match Day

For a loyalty reward, Hotstar's subscriptions team wants the users who had a paid pass running on every playoff match day.

  • Difficulty: Hard
  • Topics: Subqueries, Joins, Dates
  • Dialect: MySQL
  • Problem: #722

Problem statement

For a loyalty reward, Hotstar's subscriptions team wants the users who had a paid pass running on every playoff match day. A user may hold several passes — back to back or overlapping — and a day counts as covered if any of the user's passes covers it (starts_on and ends_on are both inclusive). A refunded pass covers nothing.

Return the user_id of every user with at least one pass who was covered on every date in MatchDay. 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: MatchDay

ColumnType
match_datedate
fixturevarchar

Primary key: match_date.

One row per playoff match day; there is always at least one.

Table: SeasonPass

ColumnType
pass_idint
user_idint
planenum(mobile, super, premium)
starts_ondate
ends_ondate
statusenum(active, refunded)

Primary key: pass_id.

One row per pass bought; ends_on is on or after starts_on.

Examples

Example 1

MatchDay

match_datefixture
2025-05-20Qualifier 1
2025-05-21Eliminator
2025-05-23Qualifier 2
2025-05-25Final

SeasonPass

pass_iduser_idplanstarts_onends_onstatus
181super2025-05-012025-05-31active
282mobile2025-05-152025-05-21active
382mobile2025-05-222025-06-21active
483premium2025-05-202025-05-24active
583premium2025-05-242025-06-23refunded
684mobile2025-05-212025-06-20active
785super2025-05-102025-05-23active
885super2025-05-182025-05-25active

Output

user_id
81
82
85

How to solve Hotstar: Subscribers Covered on Every Playoff Match Day

This is relational division: users related to all rows of another table. SQL has no FOR ALL, so it is phrased as a double negative — keep a user if there is no match day for which there is no active pass of theirs containing it. The inner NOT EXISTS asks whether one specific day is covered by any of the user's passes, so coverage spread across two passes (user 82, whose passes meet between the 21st and the 22nd) works naturally, and the status test inside it makes a refunded pass cover nothing (user 83 loses the final).

The outer query runs over pass rows, so DISTINCT returns each user once. Users with no pass at all never appear, which the statement wants.

The alternative counts: join each active pass to the match days inside it, group by user, and require the number of distinct covered dates to equal the number of match days. DISTINCT is essential — user 85's overlapping passes both cover the 20th, 21st and 23rd, and a plain COUNT(*) would reach 7 and miss the comparison. The count version is one join and a group-by; the double NOT EXISTS is a probe per user and day.

Reference solution (MySQL)

SELECT DISTINCT p.user_id
FROM SeasonPass p
WHERE NOT EXISTS (
  SELECT 1 FROM MatchDay m
  WHERE NOT EXISTS (
    SELECT 1 FROM SeasonPass q
    WHERE q.user_id = p.user_id
      AND q.status = 'active'
      AND m.match_date BETWEEN q.starts_on AND q.ends_on
  )
)

Another way

SELECT q.user_id
FROM SeasonPass q
JOIN MatchDay m ON m.match_date >= q.starts_on AND m.match_date <= q.ends_on
WHERE q.status = 'active'
GROUP BY q.user_id
HAVING COUNT(DISTINCT m.match_date) = (SELECT COUNT(*) FROM MatchDay)

← Hotstar: Stage Times of Slow Highlights Clips · LinkedIn: Invitations Pending for More Than 30 Days →