Hotstar: Plans With No Active Subscriber

Easy
700.

Hotstar: Plans With No Active Subscriber

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

ColumnType
plan_idint
plan_namevarchar
monthly_priceint
device_limitint

Primary key: plan_id.

One row per plan on sale. monthly_price is in rupees.

Table: Subscription

ColumnTypeValues
sub_idint
user_idint
plan_idint
statusenumactive, cancelled, expired

Primary key: sub_id.

One row per subscription a user has taken. plan_id always names a plan.

Example

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

Plan

plan_idplan_namemonthly_pricedevice_limit
1Mobile1491
2Super2992
3Premium4994
4Sports Pass1991
5Ad-Free Premium5994

Subscription

sub_iduser_idplan_idstatus
1111active
2122cancelled
3133active
4142expired
5154active
6161expired

Output

plan_namemonthly_price
Super299
Ad-Free Premium599
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: 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

ColumnType
plan_idint
plan_namevarchar
monthly_priceint
device_limitint

Primary key: plan_id.

One row per plan on sale. monthly_price is in rupees.

Table: Subscription

ColumnType
sub_idint
user_idint
plan_idint
statusenum(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_idplan_namemonthly_pricedevice_limit
1Mobile1491
2Super2992
3Premium4994
4Sports Pass1991
5Ad-Free Premium5994

Subscription

sub_iduser_idplan_idstatus
1111active
2122cancelled
3133active
4142expired
5154active
6161expired

Output

plan_namemonthly_price
Super299
Ad-Free Premium599

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 NULL

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