07 Reconcile

-- 07_reconcile: Aggregate children before joining
-- Read-only reference query. Setup must be loaded first.
SELECT i.order_id,i.line_sales,p.paid_amount,
 i.line_sales-p.paid_amount AS difference
FROM (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
) AS i
JOIN (
 SELECT order_id,SUM(amount) AS paid_amount
 FROM aabha_sql.payments WHERE status='succeeded' GROUP BY order_id
) AS p ON p.order_id=i.order_id
ORDER BY i.order_id;