Data Driven Decision Making Questions

WITH user_days AS (
  -- one row per user per UTC date (deduplicated)
  SELECT DISTINCT user_id,
         CAST(event_timestamp AS DATE) AS event_date
  FROM events
),
days AS (
  -- days in the last 30 days that appear in data (adjust to include all calendar days if you have a calendar table)
  SELECT DISTINCT event_date
  FROM user_days
  WHERE event_date >= CURRENT_DATE - INTERVAL '29' DAY
)
SELECT
  d.event_date,
  -- DAU = users active on that date
  (SELECT COUNT(*) FROM user_days u WHERE u.event_date = d.event_date) AS dau,
  -- MAU_30d = distinct users active in the 30-day window ending on that date (inclusive)
  (SELECT COUNT(DISTINCT u2.user_id)
   FROM user_days u2
   WHERE u2.event_date BETWEEN d.event_date - INTERVAL '29' DAY AND d.event_date
  ) AS mau_30d,
  -- ratio as decimal; avoid division by zero
  CASE
    WHEN (SELECT COUNT(DISTINCT u2.user_id)
          FROM user_days u2
          WHERE u2.event_date BETWEEN d.event_date - INTERVAL '29' DAY AND d.event_date) = 0
    THEN 0
    ELSE CAST((SELECT COUNT(*) FROM user_days u WHERE u.event_date = d.event_date) AS DECIMAL) /
         (SELECT COUNT(DISTINCT u2.user_id)
          FROM user_days u2
          WHERE u2.event_date BETWEEN d.event_date - INTERVAL '29' DAY AND d.event_date)
  END AS dau_mau_ratio
FROM days d
ORDER BY d.event_date;

-- Monthly revenue
SELECT date_trunc('month', purchase_date) AS month,
       SUM(amount) AS revenue,
       COUNT(DISTINCT user_id) AS buyers,
       AVG(amount) AS aov
FROM purchases
GROUP BY 1
ORDER BY 1 DESC;

-- Cohort revenue by purchase month
SELECT cohort, purchase_month,
       SUM(amount) AS revenue,
       COUNT(DISTINCT p.user_id) AS buyers
FROM (
  SELECT user_id, date_trunc('month', signup_date) AS cohort, channel FROM users
) u
JOIN purchases p ON u.user_id = p.user_id
CROSS JOIN LATERAL (SELECT date_trunc('month', p.purchase_date) AS purchase_month)
GROUP BY 1,2
ORDER BY 1,2;

SELECT date_trunc('month', p.purchase_date) AS month,
       u.channel,
       SUM(p.amount) AS revenue,
       COUNT(DISTINCT p.user_id) AS buyers,
       AVG(p.amount) AS aov
FROM purchases p
JOIN users u ON p.user_id = u.user_id
GROUP BY 1,2
ORDER BY 1 DESC, 3 DESC;

SELECT user_id, SUM(amount) AS spend
FROM purchases
WHERE purchase_date >= date_trunc('month', current_date - interval '1 month')
  AND purchase_date < date_trunc('month', current_date)
GROUP BY 1;
-- then compute percentiles
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY spend) AS median,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY spend) AS p90,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY spend) AS p99
FROM (
  -- subquery above
) t;

-- Refunds vs purchases by month
SELECT date_trunc('month', p.purchase_date) AS month,
       SUM(p.amount) AS gross_revenue,
       COALESCE(SUM(r.amount),0) AS refunds,
       SUM(p.amount) - COALESCE(SUM(r.amount),0) AS net_revenue,
       COALESCE(SUM(r.amount),0) / NULLIF(SUM(p.amount),0) AS refund_rate
FROM purchases p
LEFT JOIN refunds r ON p.purchase_id = r.purchase_id
GROUP BY 1
ORDER BY 1 DESC;

-- Repeat buyers in month
WITH monthly_buyers AS (
  SELECT date_trunc('month', purchase_date) AS month, user_id
  FROM purchases
  GROUP BY 1,2
)
SELECT month,
       COUNT(*) AS buyers,
       COUNT(*) FILTER (WHERE user_first_purchase_month < month) AS returning_buyers,
       (COUNT(*) FILTER (WHERE user_first_purchase_month < month))::float / COUNT(*) AS returning_rate
FROM (
  SELECT mb.month, mb.user_id,
         MIN(date_trunc('month', p.purchase_date)) OVER (PARTITION BY p.user_id) AS user_first_purchase_month
  FROM monthly_buyers mb
  JOIN purchases p ON mb.user_id = p.user_id
) t
GROUP BY 1
ORDER BY 1 DESC;