Interview fanout-safe order revenue

Return order_id, order_total, items_total, and diff after aggregating order_items to order grain before joining.

Start by naming the output grain, then build CTEs that can be inspected one at a time.

Sample input

orders joined to pre-aggregated item totalsAggregate order_items to one row per order before comparing to orders.total_amount.

order_id	order_total	example_item_rows
1001	80	SKU-A 80, SKU-B 30, SKU-C 120
1004	220	SKU-A 80, SKU-D 60, SKU-E 80

Expected output

Expected outputFirst rows show mismatches caused by recomputing from item grain safely.

order_id	order_total	items_total	diff
1001	80	230	-150
1002	120	140	-20
1004	220	220	0

Constraints

State the output contract first, use readable CTE layers, return the exact columns requested by the prompt, and use deterministic ordering.

Expected skills

Interview clarification, SQL structure, edge-case handling, and final-answer narration.

SQL

AI evaluation

Run the SQL query to inspect preview rows.