Interview Query

Subscription Retention

10
Have you seen this question before?

Given a table of subscriptions, write a query to get the retention rate of each monthly cohort for each plan_id for the three months after sign-up.

Order your output by start_month, plan_id, then num_month.

If an end_date is in the same month as start_date we say the subscription was not retained in the first month.

If the end_date occurs in the month after the month of start_date, the subscription was not retained in the second month. And so on for the third.

The end_date field is NULL if the user has not canceled.

Example:

Input:

subscriptions table

Column Type
user_id INTEGER
start_date DATETIME
end_date DATETIME
plan_id VARCHAR

Output:

Column Type
start_month DATETIME
num_month INTEGER
plan_id VARCHAR
retained FLOAT
Next question: Quantify Uncertainty
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query