Codecademy export

test.sqlite

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 subcription_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 subcription_end IS NULL) AND (segment = 30) THEN 1
   ELSE 0
  END AS is_active_30
 FROM cross_join
) SELECT * FROM status LIMIT 10;