A Revenue Dashboard That Double-Counts Refunds
A finance team reports that a SQL dashboard overstates net revenue by about 12% and omits some regions. The query joins customers to orders and then to refunds: SELECT c.region, SUM(o.amount) AS gross FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id LEFT JOIN refunds r ON r.order_id = o.order_id WHERE r.status <> 'cancelled' GROUP BY c.region; orders has one row per order; refunds has zero to three rows per order and a nullable status. Some regions have customers but no orders; some orders have only cancelled refunds; some refunds have NULL status. The dashboard also uses NOT IN (SELECT order_id FROM refunds WHERE status = 'cancelled') elsewhere. Finance expects net revenue = order amount minus non-cancelled refunds, and wants regions with zero revenue included. The analyst is asked to fix the query without changing the dashboard's grain.
1. Explain why the query overstates gross revenue and drops regions. Refer to join cardinality and NULL semantics.
Read the worked answer
The WHERE clause on r.status turns the LEFT JOIN into an inner join. For customers with no orders, r.status is NULL; for orders with no refunds, r.status is NULL; for refunds with NULL status, r.status is NULL. The predicate NULL <> 'cancelled' evaluates to unknown, so those rows are removed. That explains missing regions and missing orders with no refunds. The refunds table has zero to three rows per order, so joining it before SUM multiplies order rows: an order with two non-cancelled refunds is counted twice, overstating gross revenue. The query also sums o.amount, not net of refunds, so it cannot match finance's net revenue. Separately, NOT IN with a subquery that can return NULL will evaluate to unknown for every row and return no rows; NOT EXISTS is safer. The fix is to aggregate refunds to one row per order before joining, and to filter inside the join or use COALESCE so non-refunded orders are preserved.
2. Rewrite the query to produce net revenue per region, including regions with zero revenue and orders with no refunds. State your assumptions.
Read the worked answer
Assume a refund counts only when status is 'approved' or 'pending'; NULL status means unknown and is excluded. Use a pre-aggregated CTE and left joins: WITH refund_totals AS ( SELECT order_id, SUM(amount) AS refund_amt FROM refunds WHERE status IN ('approved','pending') GROUP BY order_id ) SELECT c.region, COALESCE(SUM(o.amount - COALESCE(r.refund_amt, 0)), 0) AS net_revenue FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id LEFT JOIN refund_totals r ON r.order_id = o.order_id GROUP BY c.region; This preserves customers with no orders; COALESCE outside SUM returns 0 for those regions. Orders with no refunds get refund_amt 0. For the other check, replace NOT IN with NOT EXISTS: SELECT ... FROM orders o WHERE NOT EXISTS (SELECT 1 FROM refunds r WHERE r.order_id = o.order_id AND r.status = 'cancelled'); If finance wants all regions including those with no customers, join from a regions table instead.