User Churn Project

Hey everyone,

 I am in the middle of the user churn project for the sql lessons and ive reached a point where im just completely stumped. The output window just shows up blank and I get no error code but also no output. Could anyone help me out with whats wrong with my 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 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
  FROM cross_join
) 
SELECT * status LIMIT 10;

Check your use of “months” and “month”. It looks like you’ve added an ‘s’ where you shouldn’t have.

You’re not going to see the results of the CTE (common table expression; the WITH statement) unless you print out each one separately. ie: select * from months.

Can you guys tell me whether I should change the where to AND to select segments appropriately, thanks?

select *
from subscriptions
limit 100;

with months as(
select
‘2017-01-01’ as first_day,
‘2017-01-31’ as last_day
union
‘2017-02-01’ as first_day,
‘2017-02-28’ as last_day
union
‘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
where segment = ‘87’
then 1
else 0
end as is_active87
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
where segment = ‘87’
END AS is_canceled87
CASE
WHEN (subscription_start < first_day)
AND (
subscription_end > first_day )
OR subscription_end IS NULL
where segment = ‘30’
then 1
else 0
where segment = ‘30’
end as is_active30
CASE
WHEN (subscription_end BETWEEN first_day AND last_day )THEN 1
ELSE 0
where segment = ‘30’
END AS is_canceled30
)