SQL Help with Bonus Question in churn Analysis Project

Hi,

I am working on the bonus question for the user churn project in SQL
The question is:
How would you modify this code to support a large number of segments?
URL: https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/projects/calc-churn-proj

I have watched the video and have successfully created a query with simpler code, but my churn rates are completely different and wrong than when I do the original, more clunky query.

Can someone please check my code to see where I’m going wrong?

I’ve uploaded a screen shot for what the correct answers should be for churn rate for both segment 87 and 30 (the only two segments in the data set).

My query is visible here, as well as below

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 (subscription_start > first_day) AND (subscription_end < last_day 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 month, segment
),
 
churn_rate AS(
  SELECT month,
  segment,
  ROUND((1.0* sum_canceled / sum_active),2) AS    churn_rate  
  FROM status_aggregate
  GROUP BY month, segment)

 SELECT * FROM churn_rate;

```![Screen Shot 2020-05-28 at 2.03.34 PM|690x151](upload://RcRSm9LenL5fHlHiFEXpP1KtxS.png)

@laurabulk,

It’s an easy fix (which are often the hardest to see). Take a closer look at the logic in your CASE statement for is_active. You’ll know when you find it.

2 Likes

ahhhh, sneaky easy fixes. Thank you so much for you quick reply and help!

1 Like