milestone 2/4 Multi-grain joins without fan-out

Combine metrics at different levels without multiplying totals.

AI Platform Economics · Calculate success rates and usage costs, prevent join fan-out, and reconcile split payments with refunds.

approach

Requests and invoices each have many rows per account. Joining raw tables creates requests × invoices and inflates sums. Aggregate each side to one row per account before joining; keep accounts with zero on either side.

WITH usage AS(
  SELECT account_id,COUNT(*) AS request_count
  FROM ai_requests
  GROUP BY account_id
), billing AS(
  SELECT account_id,SUM(billed_amount) AS billed_amount
  FROM invoices
  GROUP BY account_id
)
SELECT
  a.id,
  COALESCE(u.request_count,0) AS request_count,
  COALESCE(b.billed_amount,0) AS billed_amount
FROM ai_accounts a
LEFT JOIN usage u ON u.account_id=a.id
LEFT JOIN billing b ON b.account_id=a.id;

data for this milestone