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
| Column | Type |
|---|---|
| match_date | date |
| fixture | varchar |
Primary key: match_date.
One row per playoff match day; there is always at least one.
Table: SeasonPass
| Column | Type |
|---|---|
| pass_id | int |
| user_id | int |
| plan | enum(mobile, super, premium) |
| starts_on | date |
| ends_on | date |
| status | enum(active, refunded) |
Primary key: pass_id.
One row per pass bought; ends_on is on or after starts_on.
Examples
Example 1
MatchDay
| match_date | fixture |
|---|---|
| 2025-05-20 | Qualifier 1 |
| 2025-05-21 | Eliminator |
| 2025-05-23 | Qualifier 2 |
| 2025-05-25 | Final |
SeasonPass
| pass_id | user_id | plan | starts_on | ends_on | status |
|---|---|---|---|---|---|
| 1 | 81 | super | 2025-05-01 | 2025-05-31 | active |
| 2 | 82 | mobile | 2025-05-15 | 2025-05-21 | active |
| 3 | 82 | mobile | 2025-05-22 | 2025-06-21 | active |
| 4 | 83 | premium | 2025-05-20 | 2025-05-24 | active |
| 5 | 83 | premium | 2025-05-24 | 2025-06-23 | refunded |
| 6 | 84 | mobile | 2025-05-21 | 2025-06-20 | active |
| 7 | 85 | super | 2025-05-10 | 2025-05-23 | active |
| 8 | 85 | super | 2025-05-18 | 2025-05-25 | active |
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 →