COHORT_RETENTION (by month of first purchase): day-0/30/60/90/180 CV and retention steps
CREATE OR REPLACE TABLE mart.f_cohort_retention AS
WITH firsts AS (
  SELECT customer_id, MIN(DATE(created_at)) AS first_order_date
  FROM mart_stg.stg_orders
  GROUP BY customer_id
),
orders AS (
  SELECT
    o.customer_id,
    DATE(o.created_at) AS order_date,
    (o.order_total_gross - o.order_discount_amount) AS net_rev
  FROM mart_stg.stg_orders o
),
joined AS (
  SELECT
    f.customer_id,
    DATE_TRUNC(f.first_order_date, MONTH) AS cohort_month,
    o.order_date,
    DATE_DIFF(o.order_date, f.first_order_date, DAY) AS day_since_first,
    o.net_rev
  FROM firsts f
  JOIN orders o USING (customer_id)
)
SELECT
  cohort_month,
  COUNT(DISTINCT customer_id)                                          AS customers_in_cohort,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 0   THEN net_rev END)   AS cv_1d,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 30  THEN net_rev END)   AS cv_30d,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 60  THEN net_rev END)   AS cv_60d,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 90  THEN net_rev END)   AS cv_90d,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 180 THEN net_rev END)   AS cv_180d,
  SUM(CASE WHEN day_since_first BETWEEN 0 AND 30  THEN 1 END) /
  NULLIF(COUNT(DISTINCT customer_id),0)                                AS activity_rate_30d,
  SUM(CASE WHEN day_since_first BETWEEN 31 AND 90 THEN 1 END) /
  NULLIF(COUNT(DISTINCT customer_id),0)                                AS activity_rate_31_90d
FROM joined
GROUP BY cohort_month
ORDER BY cohort_month;