Project: Usage funnels with Warby Parker | Codecademy | Analyze data with SQL | 6. Analyze Real Data with SQL | Usage funnels

funnels_WarbyParker.txt

WITH
MONTHS AS
(
  SELECT
    '2017-01-01' AS FIRST_DATE,
    '2017-01-31' AS LAST_DATE
  UNION
  SELECT
    '2017-02-01' AS FIRST_DATE,
    '2017-02-29' AS LAST_dATE
  UNION
  SELECT
    '2017-03-01' AS FIRST_dATE,
    '2017-03-31' AS LAST_DATE
),

CROSS_JOIN AS
(
  SELECT *
  FROM SUBSCRIPTIONS
  CROSS JOIN MONTHS
),

STATUS AS
(
  SELECT
    ID,
    FIRST_DATE AS MONTH,
    CASE
      WHEN (SUBSCRIPTION_START < FIRST_DATE)
        AND (SUBSCRIPTION_END > FIRST_DATE
          OR SUBSCRIPTION_END IS NULL)
        AND (SEGMENT = 87)
      THEN 1 ELSE 0
    END AS IS_ACTIVE_87,


    CASE
      WHEN (SUBSCRIPTION_START < FIRST_DATE)
        AND (SUBSCRIPTION_END > FIRST_DATE
          OR SUBSCRIPTION_END IS NULL)
        AND (SEGMENT = 30)
      THEN 1 ELSE 0
    END AS IS_ACTIVE_30,


    CASE
      WHEN (SUBSCRIPTION_END BETWEEN FIRST_DATE AND LAST_DATE)
        AND (SEGMENT = 87)
      THEN 1 ELSE 0
    END AS IS_CANCELED_87,


    CASE
      WHEN (SUBSCRIPTION_END BETWEEN FIRST_DATE AND LAST_DATE)
        AND (SEGMENT = 30)
      THEN 1 ELSE 0
    END AS IS_CANCELED_30
  FROM CROSS_JOIN
),
STATUS_AGGREGATE AS
(
  SELECT
    MONTH,
    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,
  SUM_ACTIVE_87 AS ACTIVE_87,
  SUM_CANCELED_87 AS CANCEL_87,
  ROUND(1.0 * SUM_CANCELED_87/SUM_ACTIVE_87, 3) AS CHURNRATE_87,
  SUM_ACTIVE_30 AS ACTIVE_30,
  SUM_CANCELED_30 AS CANCEL_30,
  ROUND(1.0 * SUM_CANCELED_30/SUM_ACTIVE_30, 3) AS CHURNRATE_30
FROM STATUS_AGGREGATE;