Reconcile order totals

Return order_id, order_total, items_total, and diff (order_total - items_total) for orders whose stored total differs from the recomputed line-item total. Order by order_id.

Aggregate line items in a CTE, join to orders, and filter to the rows where the two totals disagree.

Sample input

ordersStored order totals (orders that have line items).

order_id	total_amount
1001	80
1002	120
1007	95
1008	150

order_itemsLine items. Order 1001 sums to 80 + 30 + 120 = 230, not its stored 80.

order_id	quantity	unit_price
1001	1	80
1001	2	15
1001	1	120
1007	1	15
1008	5	20

Expected output

Expected outputOrders whose stored total disagrees with the line-item sum; a negative diff means the stored total is lower.

order_id	order_total	items_total	diff
1001	80	230	-150
1002	120	140	-20
1007	95	15	80
1008	150	180	-30

Constraints

Recompute SUM(quantity * unit_price) per order in a CTE, JOIN it to orders, and keep only rows where total_amount <> items_total. Include the signed diff column.

Expected skills

Reconciliation by independent recomputation and surfacing signed differences.

SQL

AI evaluation

Run the SQL query to inspect preview rows.