SQL Churn Rate Exercise Help

I wrote this but it is not returning anything in query results, any help is appreciated!

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,
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 (segment = 87) THEN 1
  ELSE 0
END AS is_canceled_87,
CASE
  WHEN (subscription_end BETWEEN first_day AND last_day)
  AND (segment = 30) THEN 1
  ELSE 0
END AS is_canceled_30
FROM cross_join
),
SELECT *
FROM status
LIMIT 10;

I suspect something happens in the status (though you can confirm by commenting out and seeing if you get tables from the previous stuff).

Hm, my memory is hazy, but when I did this I also selected subscription_start, subscription_end, and segment, in status. I don’t know if that’s enough to break/fix it.

You could just comment out the cases and check one by one if they are throwing a spanner in the works.

yeah the problem is in status because if I just run the code before that it works

Did you try this:

yeah I did, still didn’t work :frowning:

What if you comment out all the cases, does it run?

I tried running this and it would not work, now I am even more confused

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/*,
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 (segment = 87) THEN 1
  ELSE 0
END AS is_canceled_87,
CASE
  WHEN (subscription_end BETWEEN first_day AND last_day)
  AND (segment = 30) THEN 1
  ELSE 0
END AS is_canceled_30*/
FROM cross_join
),
SELECT *
FROM status
LIMIT 10;


Yea that’s weird @lisalisaj any ideas? is this one of those where it just runs after status_aggregate and not before? or is it a matter of refreshing the page and trying again?

Believe me, this one drives me nuts too. haha. BUT, that said,
if you go step by step (like in the video) and SELECT *____ from each step(ie: question), you will see the results in the output window. However, your results won’t all print out for each step b/c they are temporary tables. You will see results for the 1st, 2nd and last question only.

@toastedpitabread i think it needs to be explicitly stated in this project that you won’t see the output of each question b/c they are temporary tables that you’re building in this huge query. UNLESS you go step by step and do a SELECT * FROM_____ for each temp. table.

2 Likes

This won’t show you results b/c there’s a comma at the end of your query after the close parens.