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
[...]