Subscription Retention
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 |
.....
Loading editor