User Churn Rate Project help

#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.