Subscriber Retention Over 90 Days

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Subscriber Retention Over 90 Days. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Disney+ Hotstar wants to measure whether new paid subscribers remain subscribed at least 90 days after their first paid subscription begins. Write a PostgreSQL query that produces retention metrics by signup cohort and acquisition channel.

Requirements

Consider only each user's first paid subscription. Exclude free-trial plans and later paid subscriptions.
Define a subscriber as retained when their subscription end date is at least 90 days after the start date. A NULL end date represents an ongoing subscription and counts as retained.
Return the cohort month, acquisition channel, cohort size, retained subscriber count, and retention rate as a percentage rounded to two decimal places.
Rank acquisition channels within each cohort by retention rate in descending order, using a window function.
Sort the final result by cohort month, rank, and acquisition channel.

Schema

users

Column	Type	Description
user_idPK	INT	Unique subscriber identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(40)	Marketing channel that acquired the user

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription record identifier
user_id	INT	References users.user_id
plan_id	INT	References plans.plan_id
start_date	DATE	Date the subscription began
end_date	DATE	Date the subscription ended, or NULL when ongoing

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
plan_name	VARCHAR(60)	Display name of the subscription plan
plan_tier	VARCHAR(20)	Plan tier such as Mobile, Super, Premium, or Trial
is_paid	BOOLEAN	Indicates whether the plan is paid

Tablesuserssubscriptionsplans

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

Subscriber Retention Over 90 Days

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Subscriber Retention Over 90 Days. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Requirements

Consider only each user's first paid subscription. Exclude free-trial plans and later paid subscriptions.
Define a subscriber as retained when their subscription end date is at least 90 days after the start date. A NULL end date represents an ongoing subscription and counts as retained.
Return the cohort month, acquisition channel, cohort size, retained subscriber count, and retention rate as a percentage rounded to two decimal places.
Rank acquisition channels within each cohort by retention rate in descending order, using a window function.
Sort the final result by cohort month, rank, and acquisition channel.

Schema

users

Column	Type	Description
user_idPK	INT	Unique subscriber identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(40)	Marketing channel that acquired the user

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription record identifier
user_id	INT	References users.user_id
plan_id	INT	References plans.plan_id
start_date	DATE	Date the subscription began
end_date	DATE	Date the subscription ended, or NULL when ongoing

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
plan_name	VARCHAR(60)	Display name of the subscription plan
plan_tier	VARCHAR(20)	Plan tier such as Mobile, Super, Premium, or Trial
is_paid	BOOLEAN	Indicates whether the plan is paid

Tablesuserssubscriptionsplans

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results