lesson 55 Advanced product analysis · advanced

Multi-grain joins without fan-out

Combine metrics at different levels without multiplying totals.

the idea

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.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

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;

Watch for: DISTINCT cannot repair inflated SUM values after a many-to-many join.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Observe join fan-out. Every request pairs with every invoice for the same account. Watch request 1 appear twice.

   step-1.sql Run

   sql›

   SELECT r.id AS request_id,i.id AS invoice_id,i.billed_amount
   FROM ai_requests r
   JOIN invoices i ON i.account_id=r.account_id
   WHERE r.account_id=1;

2. 2

   Summarize requests first. One row per account is a safe grain for the final join.

   step-2.sql Run

   sql›

   SELECT account_id,COUNT(*) AS request_count
   FROM ai_requests
   GROUP BY account_id;

3. 3

   Summarize invoices first. This is also one row per account.

   step-3.sql Run

   sql›

   SELECT account_id,SUM(billed_amount) AS billed_amount
   FROM invoices
   GROUP BY account_id;

4. 4

   Join summaries. Neither side can multiply the other now.

   step-4.sql Run

   sql›

   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