Scribd
Upload
59 views8 pages

Analyzing Codeflix Churn Rates

The document outlines a process for calculating subscription churn rates for Codeflix, focusing on two user segments. It describes a SQL query that uses a dataset of subscriptions to determine active and canceled subscriptions over several months. The final output provides the churn rate for each segment by month, based on the number of canceled subscriptions relative to active ones.

Uploaded by

Mario Ramos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
59 views8 pages

Analyzing Codeflix Churn Rates

The document outlines a process for calculating subscription churn rates for Codeflix, focusing on two user segments. It describes a SQL query that uses a dataset of subscriptions to determine active and canceled subscriptions over several months. The final output provides the churn rate for each segment by month, based on the number of canceled subscriptions relative to active ones.

Uploaded by

Mario Ramos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Calculating Churn Rates

Four months into launching Codeflix, management asks you to look into subscription churn rates. It’s early on in the business and people
are excited to know how the company is doing.

The marketing department is particularly interested in how the churn compares between two segments of users. They provide you with a
dataset containing subscription data for users who were acquired through two distinct channels.

The dataset provided to you contains one SQL table, subscriptions. Within the table, there are 4 columns:

 id - the subscription id
 subscription_start - the start date of the subscription
 subscription_end - the end date of the subscription
 segment - this identifies which segment the subscription owner belongs to

Codeflix requires a minimum subscription length of 31 days, so a user can never start and end their subscription in the same month.
--
-- Churn with a large number of segments
WITH months AS(
SELECT
'2017-01-01' AS first_day,
'2017-01-31' AS last_day
UNION
SELECT
'2017-02-01' AS first_day,
'2017-02-28' AS last_day
UNION
SELECT
'2017-03-01' AS first_day,
'2017-03-31' AS last_day),
cross_join AS (
SELECT * FROM subscriptions
CROSS JOIN months),
status AS (
SELECT id, first_day AS 'month', segment,
CASE
WHEN (first_day > subscription_start AND (
first_day > subscription_end OR
subscription_end IS NULL))
THEN 1 ELSE 0 END AS 'is_active',
CASE
WHEN (subscription_end BETWEEN
first_day AND last_day)
THEN 1 ELSE 0 END AS 'is_canceled'
FROM cross_join),
status_aggregate AS (
SELECT month, segment,
SUM(is_active) AS 'sum_active',
SUM(is_canceled) AS 'sum_canceled'
FROM status
GROUP BY segment, month)
SELECT month, segment,
ROUND(1.0*sum_canceled/sum_active,2) AS 'churn_rate'
FROM status_aggregate;

You might also like