SQL Aggregations SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Revenue Without Join Fan-Out

Return one reconciled item-revenue and captured-payment row per order.

  • Joins
  • Subqueries
  • Aggregation
  • CASE expressions
  • NULL handling

Exercise brief

Understand the request

Finance data quality lead A reconciliation report overstates measures after joining order items and payment attempts directly to orders.

Repair an order reconciliation that multiplies item revenue and captured payments across two one-to-many joins.

Return

  • Return order_id, item_revenue, captured_payment_amount in this exact left-to-right order.

Constraints

  • Aggregate order_items and payments independently to order_id before joining either result to orders.
  • Item revenue uses rows with non-NULL quantity and unit_price; retain zero values.
  • Captured payment amount includes only CAPTURED attempts.
  • Preserve every order, return zero for a missing child total, and sort by order_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER

order_items

  • order_idINTEGER
  • quantityINTEGER
  • unit_priceDECIMAL(10,2)

payments

  • order_idINTEGER
  • payment_amountDECIMAL(12,2)
  • payment_statusVARCHAR(30)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Two one-to-many child tables become many-to-many when joined together at their native grains.

Hint 2

Build one item total and one captured-payment total per order in separate CTEs, then join those order-grain results.

Hint 3

WITH item_totals AS ( SELECT order_id, SUM(/* valid item revenue */) AS item_revenue FROM order_items GROUP BY order_id ), payment_totals AS ( SELECT order_id, SUM(/* captured amount */) AS captured_payment_amount FROM payments GROUP BY order_id ) SELECT /* one row per order */ FROM orders o LEFT JOIN item_totals i ON /* order key */ LEFT JOIN payment_totals p ON /* order key */;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH item_totals AS (SELECT order_id, SUM(CASE WHEN quantity IS NOT NULL AND unit_price IS NOT NULL THEN quantity * unit_price ELSE 0 END) AS item_revenue FROM order_items GROUP BY order_id), payment_totals AS (SELECT order_id, SUM(CASE WHEN payment_status = 'CAPTURED' THEN payment_amount ELSE 0 END) AS captured_payment_amount FROM payments GROUP BY order_id) SELECT o.order_id, COALESCE(i.item_revenue, 0) AS item_revenue, COALESCE(p.captured_payment_amount, 0) AS captured_payment_amount FROM orders o LEFT JOIN item_totals i ON i.order_id = o.order_id LEFT JOIN payment_totals p ON p.order_id = o.order_id ORDER BY o.order_id;

Why this works

Correctness: reducing each child fact to one row per order before joining prevents item-payment multiplication. Edge case: orders 1001 and 1006 have multiple items and multiple payment attempts, while orders 1004 and 1010 test missing valid totals. Portability: CTEs, grouped pre-aggregation, and COALESCE are broadly portable; optimizer materialization choices do not change the cardinality contract.

Success check

Every order appears once and neither child measure changes when the other child has multiple rows.

Expected result

Use this output to verify values, aliases, ordering, and row count.

order_iditem_revenuecaptured_payment_amount
1001190190
1002100100
100300
100400
10053030
1006150150
10078080
1008240240
10096060
1010900

Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.