Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created May 3, 2020 12:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save codecademydev/6e5bc2c9798e1bd9aa88caf299d2bc51 to your computer and use it in GitHub Desktop.
Save codecademydev/6e5bc2c9798e1bd9aa88caf299d2bc51 to your computer and use it in GitHub Desktop.
Codecademy export
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 * FROM status LIMIT 10;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment