Answers and explanations · Prevent join multiplication and reconcile money

Exercise 07a

Two order lines multiply two successful payments into four pairs. The true successful total 840 is doubled to 1680. The failed attempt must remain excluded.

SELECT i.order_id,COUNT(*) AS joined_rows,SUM(p.amount) AS inflated_payments
FROM aabha_sql.order_items AS i JOIN aabha_sql.payments AS p ON p.order_id=i.order_id
WHERE i.order_id=1006 AND p.status='succeeded' GROUP BY i.order_id;

Expected output:

Runnable answer: sql/solutions/07a.sql

Exercise 07b

Each summary has one order row before joining. Line sales and paid amount are both 840. This avoids accidental replication of either measure.

SELECT i.order_id,i.line_sales,p.paid_amount FROM (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items WHERE order_id=1006 GROUP BY order_id
) AS i JOIN (
 SELECT order_id,SUM(amount) AS paid_amount FROM aabha_sql.payments
 WHERE order_id=1006 AND status='succeeded' GROUP BY order_id
) AS p ON p.order_id=i.order_id;

Expected output:

Runnable answer: sql/solutions/07b.sql