Start with the shared case
Use the fixture and metric contract from Job simulation 1: agree what revenue means. Reuse a scratch SQLite database only if the two teaching tables are already loaded; do not create them twice. You are reviewing code, not investigating real customer records.
The faulty query
Predict the result before running this query. The refund-date condition deliberately stays in ON; moving it to WHERE would create another defect by dropping unmatched orders.
SELECT SUM(o.amount)-SUM(COALESCE(r.amount,0)) AS net
FROM orders o LEFT JOIN refunds r
ON r.order_id=o.order_id AND r.refunded_at < '2026-02-01'
WHERE o.status='completed'
AND o.ordered_at >= '2026-01-01' AND o.ordered_at < '2026-02-01';Your review task
Explain which order appears more than once, calculate the wrong total by hand, and repair the query without DISTINCT on money amounts. Then report completed orders, distinct customers, gross, refunds, net and net per completed order. Do not average customer totals and call it average order value.
Worked solution
Order o2 joins to two refund rows, so its 60 is counted twice. Joined gross is 470; refunds remain 35; faulty net is 435. Aggregate refunds to one row per order before joining. Expected output: 5, 3, 410, 35, 375, 75.0. SUM(DISTINCT amount) is not a grain repair: different orders may legitimately have equal amounts.
WITH refund_by_order AS (
SELECT order_id,SUM(amount) AS refunded
FROM refunds WHERE refunded_at < '2026-02-01'
GROUP BY order_id
), eligible AS (
SELECT o.order_id,o.customer,o.amount,
COALESCE(r.refunded,0) AS refunded
FROM orders o LEFT JOIN refund_by_order r
ON r.order_id=o.order_id
WHERE o.status='completed'
AND o.ordered_at >= '2026-01-01' AND o.ordered_at < '2026-02-01'
)
SELECT COUNT(*) AS completed_orders,COUNT(DISTINCT customer) AS customers,
SUM(amount) AS gross,SUM(refunded) AS refunds,
SUM(amount-refunded) AS net,
1.0*SUM(amount-refunded)/COUNT(*) AS net_per_order
FROM eligible;Test the repair, not only this answer
Add a completed January order o7 for customer D with amount 60 and no refund in a fresh copy. Predict net=435, count=6, customers=4 and net per order=72.5. A DISTINCT money fix will fail this equal-price case. Next add another included refund of 5 to o2: net falls by 5 while completed-order count stays unchanged. Keep original and extension outputs separate.
Explain the trade-off
In production, ask about refund currency, duplicates, partial refunds above order value, order status changes, timestamp timezone and late arrivals. Integer EUR avoids rounding distractions here; real systems need explicit minor units or decimal arithmetic. An exact match on this tiny fixture does not prove production correctness.
Try the activity before reading its reference. Keep your sources, commands and assumptions with your work. Companion activities are unsaved self-practice and do not award a certificate or change lesson progress.