lesson 55 Advanced product analysis · advanced

Multi-grain joins without fan-out

Combine metrics at different levels without multiplying totals.

the idea

Joining a parent to two independent child tables multiplies rows: each settlement row is repeated once per refund, so summing settlement amounts after the join double-counts. Aggregate each child table to the shared grain first, then join the summaries.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Find settlements with many refunds. Run this. refunds > 1 means one charge spawned several partial refund rows—the pattern that breaks naive joins.the pattern

   example.sql Run

   sql›

   SELECT s.settlement_id, COUNT(r.refund_id) AS refunds
   FROM settlements s
   LEFT JOIN refunds r ON r.settlement_id = s.settlement_id
   GROUP BY s.settlement_id
   HAVING COUNT(r.refund_id) > 1
   LIMIT 10;

2. 2

   Watch totals double-count. Run this. joined_sum adds settlement amounts once per refund row; true_sum is the correct ledger total.

   step-2.sql Run

   sql›

   SELECT
     SUM(s.amount_cents) AS joined_sum,
     (
       SELECT SUM(amount_cents)
       FROM settlements
     ) AS true_sum
   FROM settlements s
   LEFT JOIN refunds r ON r.settlement_id = s.settlement_id;

3. 3

   Roll refunds up first. Run this. One row per settlement_id with total refunded cents—safe to join without multiplying settlements.

   step-3.sql Run

   sql›

   SELECT
     settlement_id,
     COUNT(*) AS refunds,
     SUM(amount_cents) AS refunded_cents
   FROM refunds
   GROUP BY settlement_id
   ORDER BY refunds DESC
   LIMIT 10;

4. 4

   Join summaries, not raw refunds. Run this. settled_cents and refunded_cents match because each settlement appears once on the left.

   step-4.sql Run

   sql›

   SELECT
     SUM(s.amount_cents) AS settled_cents,
     SUM(COALESCE(r.refunded_cents, 0)) AS refunded_cents
   FROM settlements s
   LEFT JOIN (
     SELECT settlement_id, SUM(amount_cents) AS refunded_cents
     FROM refunds
     GROUP BY settlement_id
   ) r ON r.settlement_id = s.settlement_id;

common mistake

SUM(s.amount_cents) after joining refunds counts a settlement with two refunds twice.