User Churn Rate Project help

Get Help SQL
#1

Hello~

So I’m working on the Churn Rate exercise here, and I’m not quite sure where I went wrong because nothing shows up when I save it. I feel like I’m missing something obvious, but I’ve been messing with it for about an hour and can’t figure it out on my own.

Here is what I have right now:

WITH months AS
 (SELECT '2016-01-01' as 'first_day',
 '2016-01-31' as 'last_day'
 UNION
 SELECT '2016-02-01' as 'first_day',
 '2016-02-28' as 'last_day'
 UNION
 SELECT '2016-03-01' as 'first_day',
 '2016-03-31' as 'last_day')
 WITH cross_join AS
 (SELECT subscriptions.*, months.*
 FROM subscriptions
 CROSS JOIN months)
 WITH status AS
 (SELECT cross_join, first_day AS 'month', CASE 
  WHEN 
  (segment = '87'
 AND subscription_start < first_day) THEN 1
  ELSE 0
  END AS is_active_87,
  CASE
  WHEN (segment = '30'
  AND subscription_start < first_day) THEN 1
  ELSE 0
  END AS is_active_30,
 CASE
 WHEN
 (segment = '87'
 AND subscription_end IS BETWEEN first_day AND last_day) THEN 1
 ELSE 0
 END AS is_canceled_87,
 CASE
 WHEN
 (segment = '30'
 AND subscription_end IS BETWEEN first_day AND last_day) THEN 1
 ELSE 0
 END AS is_canceled_30
 FROM cross_join)
 WITH status_aggregate AS
 (SELECT sum(is_active_87) AS sum_active_87, sum(is_active_30) AS sum_active_30 ,sum(is_canceled_87) AS sum_canceled_87  ,sum(is_canceled_30) AS sum_canceled_30
  FROM status
  GROUP BY month)
  SELECT month, 1.0 * (sum_canceled_87 / sum_active_87), 1.0 * (sum_canceled_30 / sum_canceled_30)
  FROM status_aggregate
  GROUP BY month;

Any help would be most appreciated. Thanks in advance! :slight_smile:

#2

Hi there! You don’t need another WITH. So, your code would be like this:

WITH months AS
(SELECT
  '2017-01-01' AS first_day,
  '2017-01-30' 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-30' AS last_day),
'HERE YOU DONT NEED ANOTHER WITH'cross_join AS
[...]
#3

I am having a similar issue on this project and when I save, it doesn’t run. I’ve been trying to debug but the project won’t even run successfully on the first WITH statement (months).

Update: I forgot the 2nd union statement

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'
   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',
   CASE 
     WHEN segment = 87 AND subscription_start<first_day AND (subscription_end > first_day OR subscription_end IS NULL) THEN 1 ELSE 0 END AS 'is_active_87',
    CASE 
     WHEN segment = 30 AND subscription_start<first_day AND (subscription_end > first_day OR subscription_end IS NULL) THEN 1 ELSE 0 END AS 'is_active_30',
    CASE 
     WHEN segment = 87 AND subscription_end BETWEEN first_day and last_day THEN 1 ELSE 0 END AS 'is_canceled_87',
     CASE 
     WHEN segment = 30 AND subscription_end BETWEEN first_day and last_day THEN 1 ELSE 0 END AS 'is_canceled_30'
   FROM cross_join
  ),
status_aggregate AS
  (
  SELECT month, SUM(is_active_87) AS 'sum_active_87',
    SUM(is_active_30) AS 'sum_active_30',
    SUM(is_canceled_87) AS 'sum_canceled_87',
    SUM(is_canceled_30) AS 'sum_canceled_30'
  FROM status  
  GROUP BY 1
  )
 
SELECT month, 
1.0*sum_canceled_87/sum_active_87,
1.0*sum_canceled_30/sum_active_30
FROM status_aggregate
;
#4

Hello,

I think i have the same issue… i’m doing dhe Churn Rate Project and I can’t see anything as a Result…

I tried to watch the youtube video with the solution and the name of the video in youtube starts with “DEPRECATED”, and i’m doing all the same code of the video (and following previous lessons), and it still does not work… i don’t know why.

2 Likes
#5

Hello,

I’m having the same issue! Weird.

#6

Hi there! You are missing a comma right before your WITH status_aggregrate AS

#7

Here is my code! Hope it helps. Rounding at the end is unnecessary but its what i wanted to do. The end results were: Segment 87 January 0.335, February 0.464 and March 0.912 ; Segment 30 January 0.082, February 0.079 and March 0.132

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,
–#subscribers 87
CASE
WHEN subscription_start < first_day AND (subscription_end > last_day OR subscription_end IS NULL) AND segment IS 87
THEN 1
ELSE 0
END AS is_active_87,
–#subscribers 30
CASE
WHEN subscription_start < first_day AND (subscription_end > last_day OR subscription_end IS NULL)
AND segment IS 30
THEN 1
ELSE 0
END AS is_active_30,
–#cancelation 87
CASE
WHEN (subscription_end BETWEEN first_day AND last_day) AND segment IS 87
THEN 1
ELSE 0
END AS is_canceled_87,
–#cancelation 30
CASE
WHEN (subscription_end BETWEEN first_day AND last_day) AND segment IS 30
THEN 1
ELSE 0
END AS is_canceled_30
FROM cross_join),
status_aggregate AS (
SELECT month,
SUM(is_active_87) AS sum_active_87,
SUM(is_active_30) AS sum_active_30,
SUM(is_canceled_87) AS sum_canceled_87,
SUM(is_canceled_30) AS sum_canceled_30
FROM status
GROUP BY month
)

–SELECT * FROM status_aggregate LIMIT 100;

SELECT month, ROUND((1.0 * sum_canceled_87)/sum_active_87,3) AS churn_rate_87, ROUND((1.0 * sum_canceled_30)/sum_active_30,3) AS churn_rate_30
FROM status_aggregate;

#8

UPDATE: After watching “[DEPRECATED] SQL Project Calculating Churn Rates” I realized that my active subscribers segment (from status temporary table) wasn’t counting those that canceled within the month. So to fix that i needed to change (subscription_end > last_day) to (subscription_end > first_day).

Now I have the correct values… Nevertheless, I’m still unsure as to why we need to count those subscribers too for the denominator part of the equation.

NEW CODE FOR THE BONUS QUESTION:

– I will try and make this query support a larger number of segments. With help from the video [DEPRECATED] SQL Project Calculating Churn Rates.

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,
–#subscribers 87 and 30
CASE
WHEN subscription_start < first_day AND (subscription_end > first_day OR subscription_end IS NULL)
THEN 1
ELSE 0
END AS is_active,

–#cancelation 87 and 30
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

)

–SELECT * FROM status_aggregate ORDER BY segment LIMIT 100;

SELECT month, ROUND((1.0 * sum_canceled)/sum_active,3) AS churn_rate, segment
FROM status_aggregate ORDER BY segment;

#9

I’m having the same issue and have been with the User Churn and Temporary Tables sections. Here’s my code (it matched with the instructional video, yet nothing populates after I run the code):

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 subscription
CROSS JOIN months),
status AS
(SELECT
id,
first_day AS month,
CASE
WHEN (subscription_start < first_day) AND (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 87)
THEN 1
ELSE 0
END AS is_active_87,
CASE
WHEN (subscription_start < first_day) AND (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 30)
THEN 1
ELSE 0
END AS is_active_30,
CASE
WHEN (subscription_end BETWEEN first day AND last_day) AND (segement = 87) THEN 1
ELSE 0
END AS is_canceled_87
CASE
WHEN (subscription_end BETWEEN first day AND last_day) AND (segement = 30) THEN 1
ELSE 0
END AS is_canceled_30
FROM cross_join),
status_aggregate AS
(SELECT (SUM (is_active_87) AS sum_active_87),
(SUM (is_active_30) AS sum_active_30),
(SUM (is_canceled_87) AS sum_canceled_87),
(SUM (is canceled_30) AS sum_canceled_30)
FROM status
GROUP BY month) SELECT month,
1.0 * sum_canceled_87 / sum_active_87 AS churn_rate_87,
1.0 * sum_canceled_30 / sum_active_30 AS churn_rate_30
FROM status_aggregate;

#10

Hello @css3290358051, I had same problem. So, instead of using AND operator for segment = 87, try to use WHERE clause.

It should look like this
… WHEN (subscription_start < first_day) AND (subscription_end > first_day OR subscription_end IS NULL) …
WHERE segment = 87

#11

Here’s my take if anyone wants to see it

SELECT *
 FROM subscriptions
 LIMIT 100;
 
 SELECT MAX(subscription_start),
 MIN(subscription_start),
 MAX(subscription_end),
 MIN(subscription_end)
 FROM subscriptions;
 
 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 > first_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 active,
   SUM(is_canceled) AS canceled
 FROM status
 GROUP BY month, segment
 )
 
 SELECT 
   month,
   segment,
   1.0 * canceled/active AS churn
 FROM status_aggregate;
1 Like
#12

Hi, I tried your code but I cannot generate the result.

#13

I did the bonus assignment by adding another temporary table, wonder if it wasn’t necessary

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 > first_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
ORDER BY segment
),
status_aggregate AS
(
SELECT month, segment, SUM(is_active) as ‘active’, SUM(is_canceled) AS ‘sum_canceled’
FROM status
GROUP BY month, segment
ORDER BY segment
),
segment_aggregate AS
(
SELECT month, segment, SUM(active) AS ‘active_by_segment’,
SUM(sum_canceled) AS ‘canceled_by_segment’
FROM status_aggregate
GROUP BY month, segment
)
SELECT month, segment,
ROUND((1.0 * canceled_by_segment / active_by_segment), 3) AS ‘churn_rate’
FROM segment_aggregate
ORDER BY segment;

1 Like
#14

Hi,
What were your results?

#15

Bonus
Bonus1244×371 23.9 KB

1 Like