Business Intelligence and Reporting Infrastructure Questions

-- Compare warehouse total vs dashboard A query
SELECT SUM(amount) AS sum_amount, COUNT(*) AS cnt
FROM fact_revenue
WHERE revenue_date BETWEEN '2025-11-01' AND '2025-11-30'
  AND revenue_type = 'recognized';

SELECT transaction_id, COUNT(*) FROM fact_revenue
WHERE date BETWEEN ...
GROUP BY transaction_id HAVING COUNT(*) > 1;

WITH first_purchase AS (
  -- cohort month per user (first purchase month)
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(purchase_date)) AS cohort_month
  FROM purchases
  GROUP BY user_id
),
purchases_with_cohort AS (
  -- join every purchase to the user's cohort and compute month offset
  SELECT
    p.user_id,
    f.cohort_month,
    DATE_TRUNC('month', p.purchase_date) AS purchase_month,
    ((EXTRACT(YEAR FROM p.purchase_date) - EXTRACT(YEAR FROM f.cohort_month)) * 12
     + (EXTRACT(MONTH FROM p.purchase_date) - EXTRACT(MONTH FROM f.cohort_month))
    )::INT AS month_diff
  FROM purchases p
  JOIN first_purchase f ON p.user_id = f.user_id
),
cohort_sizes AS (
  -- size of each cohort
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_users
  FROM first_purchase
  GROUP BY cohort_month
),
cohort_activity AS (
  -- which users in a cohort were active in which month_diff (0..5)
  SELECT
    cohort_month,
    month_diff,
    COUNT(DISTINCT user_id) AS active_users
  FROM purchases_with_cohort
  WHERE month_diff BETWEEN 0 AND 5
  GROUP BY cohort_month, month_diff
)
SELECT
  cs.cohort_month,
  cs.cohort_users,
  COALESCE(ROUND(100.0 * ca0.active_users / cs.cohort_users, 2), 0) AS m0_pct,
  COALESCE(ROUND(100.0 * ca1.active_users / cs.cohort_users, 2), 0) AS m1_pct,
  COALESCE(ROUND(100.0 * ca2.active_users / cs.cohort_users, 2), 0) AS m2_pct,
  COALESCE(ROUND(100.0 * ca3.active_users / cs.cohort_users, 2), 0) AS m3_pct,
  COALESCE(ROUND(100.0 * ca4.active_users / cs.cohort_users, 2), 0) AS m4_pct,
  COALESCE(ROUND(100.0 * ca5.active_users / cs.cohort_users, 2), 0) AS m5_pct
FROM cohort_sizes cs
LEFT JOIN cohort_activity ca0 ON cs.cohort_month = ca0.cohort_month AND ca0.month_diff = 0
LEFT JOIN cohort_activity ca1 ON cs.cohort_month = ca1.cohort_month AND ca1.month_diff = 1
LEFT JOIN cohort_activity ca2 ON cs.cohort_month = ca2.cohort_month AND ca2.month_diff = 2
LEFT JOIN cohort_activity ca3 ON cs.cohort_month = ca3.cohort_month AND ca3.month_diff = 3
LEFT JOIN cohort_activity ca4 ON cs.cohort_month = ca4.cohort_month AND ca4.month_diff = 4
LEFT JOIN cohort_activity ca5 ON cs.cohort_month = ca5.cohort_month AND ca5.month_diff = 5
ORDER BY cs.cohort_month;