Company Business Model and Product Market Understanding Questions

WITH cohorts AS (
  SELECT user_id,
         to_char(signup_date,'YYYY-MM') AS cohort_month,
         date_trunc('month', signup_date)::date AS signup_month
  FROM users
),
tx AS (
  SELECT t.user_id,
         date_trunc('month', t.occurred_at)::date AS tx_month,
         -- net amount: discounts reduce revenue; refunds negate the net amount
         CASE WHEN t.is_refund THEN -(t.amount - COALESCE(t.discount_amount,0))
              ELSE  (t.amount - COALESCE(t.discount_amount,0)) END AS net_amount
  FROM transactions t
),
months AS (
  SELECT generate_series(0,11) AS month_number
),
user_months AS (
  -- cross join users to months to ensure zeroes for missing months up to 11
  SELECT c.user_id, c.cohort_month, c.signup_month, m.month_number,
         (c.signup_month + (m.month_number || ' months')::interval)::date AS window_month
  FROM cohorts c CROSS JOIN months m
),
agg AS (
  SELECT um.cohort_month,
         um.month_number,
         um.user_id,
         COALESCE(SUM(tx.net_amount) FILTER (WHERE tx.tx_month = um.window_month),0) AS net_in_month
  FROM user_months um
  LEFT JOIN tx ON tx.user_id = um.user_id
  GROUP BY 1,2,3
),
cohort_stats AS (
  SELECT cohort_month,
         month_number,
         SUM(net_in_month) AS cohort_net,
         COUNT(DISTINCT user_id) AS cohort_size
  FROM agg
  GROUP BY 1,2
)
SELECT cohort_month,
       month_number,
       CASE WHEN cohort_size = 0 THEN 0
            ELSE ROUND(cohort_net::numeric / cohort_size,2) END AS ltv_per_user
FROM cohort_stats
ORDER BY cohort_month, month_number;