A practice prompt we wrote. No company or candidate report names it, so it carries no company tag.

How to answer

You’re handed this report:

SELECT date_trunc('month', o.ordered_at) AS month,
       sum(i.quantity * i.unit_price)    AS revenue,
       sum(p.amount)                     AS collected
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN payments p    ON p.order_id = o.order_id
GROUP BY 1;
  1. Reconcile first. Compute the total from the source table alone, with no join, and compare. That gives you the true number and the size of the error before you touch the query.
  2. State each table’s grain out loud. “Orders: one row per order. Items: one row per order line. Payments: I’d guess one per order, let me check.” The bug is a table whose rows aren’t unique on the join key.
  3. Prove it with a query. GROUP BY the join key HAVING count(*) > 1 on each joined table. The table that returns rows is the one fanning out.
  4. Read the multiplier. An exact multiple across the board points at a systematic duplicate: every order has the same number of matching rows, often a status or event type you forgot to filter on. An uneven inflation points at a genuine one-to-many, or at rows being lost somewhere else at the same time, so check what each inner join drops.
  5. Fix at the grain. Pre-aggregate each many side to the join key before the join, or filter it to one row per key. When two one-to-many tables join to the same parent, they multiply each other, so each needs its own subquery. Don’t reach for DISTINCT; it hides the grain problem instead of fixing it.
  6. Leave a check. A test that the joined total equals the source total, and a uniqueness test on each many side’s real grain.

Practice it with a customer

In Pro, the data platform migration case puts reconciliation like this in front of a finance lead whose month-end reports have to tie to the ledger, to the cent, on the new platform as on the old one.

Follow-ups

What the interviewer may ask next, once your first answer is on the table.

  • Refunds arrive as negative payment rows, and finance wants cash collected by capture date, not order date. What changes?
  • How would you stop this from reaching a dashboard again?
  • The inflation is not an exact multiple, just somewhat too high. What does that tell you?

Where answers go wrong

  • Adds DISTINCT to the select or the sum until the total looks right, which hides the duplication and drops real rows with equal amounts.

Answer this in two minutes

Write the answer you would say out loud. The clock starts with your first word.

Two minutes

Compare with the model answer

Model answer

“First I get the true figure: item revenue per month from order_items joined only to orders, which is safe because each item has exactly one order.”

SELECT date_trunc('month', o.ordered_at) AS month,
       sum(i.quantity * i.unit_price)    AS revenue
FROM orders o
JOIN order_items i USING (order_id)
GROUP BY 1
ORDER BY 1;

“The report minus this, per month, is the size of the bug. If the report is an exact multiple of it, every order is matching the same number of extra rows.”

“Grain: orders is one row per order and order_items is one per line, so the items join is expected and correct for summing item revenue. payments I don’t know, so I check:”

SELECT order_id, count(*) AS rows_per_order
FROM payments
GROUP BY order_id
HAVING count(*) > 1
LIMIT 20;

SELECT kind, count(*) FROM payments GROUP BY kind;

“That shows the cause: payments stores an authorization row and a capture row per order. So each item line is repeated once per payment row, which multiplies revenue, and each payment is repeated once per item line, which inflates collected too. That’s the many-to-many: items and payments both hang off the order and multiply each other.”

“To see it, I build the smallest month that shows the problem and run both queries on it:”

Three orders in August
order 1: 3 lines, total 50
         auth 50 + capture 50
order 2: 1 line, total 50
         auth 50 + capture 50
order 3: 1 line, total 50
         no payment yet

             true   report
revenue      150    200
collected    100    400

“Revenue doubles for orders 1 and 2, and order 3 disappears, so the report is 1.33x the truth, not 2x. And collected is 4x too high, because it sums authorizations as if they were cash, and repeats each of order 1’s payments once per item line.”

“That’s the second bug hiding under the first. The join to payments is an inner join, so an order with no payment row yet drops out of revenue entirely. The two errors pull in opposite directions, which is why the total can be too high by something that isn’t a clean multiple. The reconciliation catches both; eyeballing the ratio catches neither.”

“The fix is to bring each side down to one row per order before joining, and to filter payments to what we mean by collected:”

WITH items AS (
  SELECT order_id, sum(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY order_id
),
captured AS (
  SELECT order_id, sum(amount) AS collected
  FROM payments
  WHERE kind = 'capture'
  GROUP BY order_id
)
SELECT date_trunc('month', o.ordered_at) AS month,
       sum(i.revenue)                    AS revenue,
       coalesce(sum(c.collected), 0)     AS collected
FROM orders o
JOIN items i         ON i.order_id = o.order_id
LEFT JOIN captured c ON c.order_id = o.order_id
GROUP BY 1
ORDER BY 1;

“Now every join is one-to-one on order_id, so nothing can repeat, and the payments join is LEFT, so an order with nothing captured keeps its revenue and shows zero collected.”

“DISTINCT would only hide it: sum(DISTINCT p.amount) merges two orders’ equal payments, and SELECT DISTINCT collapses two real lines for the same product and price.”

“If refunds arrive as negative rows and finance wants cash by capture date, collected stops being an order metric. I’d take it out of this query and sum amount for captures and refunds from payments alone, grouped by the month of the payment’s own timestamp. Revenue by order month and cash by payment month are two reports with two dates, and forcing them into one GROUP BY puts one of them in the wrong month.”

“To keep it fixed, I’d add a reconciliation test that each month’s report revenue equals the direct sum from order_items, a uniqueness test on payments (order_id, kind), and an accepted-values test on kind. In dbt that’s dbt_utils.unique_combination_of_columns on payments with order_id and kind, accepted_values on kind, and a singular test: a SQL file that returns the months where report revenue differs from the direct sum, and fails if it returns any. Then a new kind of payment row, such as a second partial capture or a refund, fails a check instead of a board meeting.”

Learn this in Module 7: SQL and data for FDEs, part of the curriculum.