Hotstar: Plans With No Active Subscriber — SQL Joins Problem
Before a pricing review, Hotstar's subscriptions team wants to know which plans in the catalogue nobody is currently paying for.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #700
Problem statement
Before a pricing review, Hotstar's subscriptions team wants to know which plans in the catalogue nobody is currently paying for. A plan with only cancelled or expired subscriptions counts as having no active subscriber, and so does a plan nobody ever bought.
Return plan_name and monthly_price of every plan with no subscription whose status is active. 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: Plan
| Column | Type |
|---|---|
| plan_id | int |
| plan_name | varchar |
| monthly_price | int |
| device_limit | int |
Primary key: plan_id.
One row per plan on sale. monthly_price is in rupees.
Table: Subscription
| Column | Type |
|---|---|
| sub_id | int |
| user_id | int |
| plan_id | int |
| status | enum(active, cancelled, expired) |
Primary key: sub_id.
One row per subscription a user has taken. plan_id always names a plan.
Examples
Example 1
Plan
| plan_id | plan_name | monthly_price | device_limit |
|---|---|---|---|
| 1 | Mobile | 149 | 1 |
| 2 | Super | 299 | 2 |
| 3 | Premium | 499 | 4 |
| 4 | Sports Pass | 199 | 1 |
| 5 | Ad-Free Premium | 599 | 4 |
Subscription
| sub_id | user_id | plan_id | status |
|---|---|---|---|
| 1 | 11 | 1 | active |
| 2 | 12 | 2 | cancelled |
| 3 | 13 | 3 | active |
| 4 | 14 | 2 | expired |
| 5 | 15 | 4 | active |
| 6 | 16 | 1 | expired |
Output
| plan_name | monthly_price |
|---|---|
| Super | 299 |
| Ad-Free Premium | 599 |
How to solve Hotstar: Plans With No Active Subscriber
The question is about absence: a plan qualifies when no active subscription points at it. That is an anti join, and NOT EXISTS says it directly — for each plan, look for one active subscription on it, and keep the plan if the lookup finds nothing. A plan with only cancelled or expired rows finds nothing (the status test is inside the subquery), and a plan nobody ever bought finds nothing either, so both are returned.
The LEFT JOIN version keeps every plan and attaches its active subscriptions; plans with none get NULLs and are picked out with s.sub_id IS NULL. Where the status test goes matters: in the ON clause it limits what is attached, while in WHERE it would discard exactly the NULL rows you are looking for.
NOT IN works here because plan_id is never NULL in Subscription; with a NULL in the list, NOT IN returns no rows at all, which is why NOT EXISTS is the safer habit. Each approach is one pass over the plans with an index lookup into subscriptions.
Reference solution (MySQL)
SELECT p.plan_name, p.monthly_price
FROM Plan p
WHERE NOT EXISTS (
SELECT 1 FROM Subscription s
WHERE s.plan_id = p.plan_id AND s.status = 'active'
)Another way
SELECT p.plan_name, p.monthly_price FROM Plan p LEFT JOIN Subscription s ON s.plan_id = p.plan_id AND s.status = 'active' WHERE s.sub_id IS NULLAnother way
SELECT plan_name, monthly_price FROM Plan WHERE plan_id NOT IN (SELECT plan_id FROM Subscription WHERE status = 'active')← Hotstar: Commentary Languages Live on Each Match · Hotstar: Buffering Share of Watch Time by CDN →