Project information

  • Category: SQL
  • Project Name: Churn Rates Project
  • Project date: February 12, 2022

Churn Rates Project

The project is from Codecademy's Analyze Data with SQL course. 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.

First things first, I'll take a quick look at the data. After running a select distinct on segment, I was able to establish there are two segments 30 and 87.



Next, I'd like to get a sense of data. I want to see the min and max for subscription start:


Query Results


In my analysis, I want to establish the churn rate. In order to do this I must first establish the first and last day of each month. I'm going to use a with (CTE) statement so I can use this table later in my query


With the first and last day established, I'm now going to cross join with table subscriptions.


Next, I'll use a CASE statement to simplify the data. I'll create a few buckets.
is_active_87 and is_canceled_87
is_active_30 and is_active_30


With all this out of the way I can finally start making useful of the data.


Query Results / Analysis


There is not much difference in churn amongst the two groups but we can see there is a significatly higher churn rate in march.