Data Problem Solving and Business Context Questions

-- 1) Define cohort start per user (e.g., signup_date)
WITH events AS (
  SELECT user_id, signup_date,
         MAX(last_activity_date) AS last_activity,
         -- compute days until churn based on gap rule: churn_date = first date where gap >=30
         MIN(activity_date) FILTER (WHERE gap_days >= 30) AS churn_date
  FROM user_activity_with_gaps
  GROUP BY user_id, signup_date
),
times AS (
  SELECT
    user_id,
    DATE_PART('day', COALESCE(churn_date, last_activity, study_end) - signup_date) AS time,
    CASE WHEN churn_date IS NOT NULL AND churn_date <= study_end THEN 1 ELSE 0 END AS event
  FROM events
)
-- 2) KM table: for each integer t compute n_at_risk and d_events
SELECT
  t,
  SUM(CASE WHEN time >= t THEN 1 ELSE 0 END) AS n_at_risk,
  SUM(CASE WHEN time = t AND event = 1 THEN 1 ELSE 0 END) AS d_events
FROM generate_series(0, max_time) AS t
CROSS JOIN times
GROUP BY t
ORDER BY t;

SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN revenue_amount IS NULL THEN 1 ELSE 0 END) AS null_count,
  ROUND(100.0 * SUM(CASE WHEN revenue_amount IS NULL THEN 1 ELSE 0 END)/COUNT(*),2) AS pct_null
FROM payments;

SELECT payment_date, payment_method, COUNT(*) AS nulls
FROM payments
WHERE revenue_amount IS NULL
GROUP BY 1,2
ORDER BY nulls DESC
LIMIT 50;

SELECT DATE(created_at) dt, COUNT(*) nulls FROM payments
WHERE revenue_amount IS NULL
GROUP BY 1 ORDER BY 1;

SELECT source_system, etl_batch_id, COUNT(*) FROM payments
WHERE revenue_amount IS NULL GROUP BY 1,2;

SELECT p.id
FROM payments p
LEFT JOIN products pr ON p.product_id = pr.id
WHERE p.revenue_amount IS NULL AND pr.price IS NULL
LIMIT 10;

SELECT status, COUNT(*) FROM payments WHERE revenue_amount IS NULL GROUP BY status;

-- Params:
-- :exclude_tests boolean (true = exclude test/internal accounts)
-- test_users table (optional) with user_id for internal/test accounts

WITH installs_filtered AS (
  SELECT i.user_id, i.install_date
  FROM installs i
  WHERE NOT (:exclude_tests = true AND EXISTS (
            SELECT 1 FROM test_users t WHERE t.user_id = i.user_id
          ))
),
user_revenue AS (
  -- sum payments per user occurring within 90 days of their install
  SELECT
    f.user_id,
    f.install_date::date AS cohort_date,
    COALESCE(SUM(p.amount), 0) AS revenue_90d
  FROM installs_filtered f
  LEFT JOIN payments p
    ON p.user_id = f.user_id
    AND p.paid_at >= f.install_date
    AND p.paid_at < f.install_date + INTERVAL '90 days'
  GROUP BY f.user_id, cohort_date
)
-- cohort LTV: average revenue per user in cohort within 90 days
SELECT
  cohort_date AS install_date,
  COUNT(*) AS users_in_cohort,
  SUM(revenue_90d) AS total_revenue_90d,
  ROUND(AVG(revenue_90d)::numeric, 2) AS ltv_90d
FROM user_revenue
GROUP BY cohort_date
ORDER BY cohort_date;