Answers and explanations · Audit quality before trusting the result

Exercise 10a

Raw row 4 refers to customer 99, who is not present. Raw row 7 has no identifier at all and is intentionally excluded from this unknown-reference query.

SELECT r.batch_row,r.customer_id FROM aabha_sql.raw_orders AS r
LEFT JOIN aabha_sql.customers AS c ON c.customer_id=r.customer_id
WHERE r.customer_id IS NOT NULL AND c.customer_id IS NULL ORDER BY r.batch_row;

Expected output:

Runnable answer: sql/solutions/10a.sql

Exercise 10b

Rows 2 and 8 survive. Row 1 is an older version, and rows 3–7 fail one or more acceptance rules. Preserve excluded raw rows and their reasons; do not call the remaining result complete real-world truth.

WITH ranked AS (
 SELECT r.*,ROW_NUMBER() OVER (PARTITION BY source_order_id ORDER BY loaded_at DESC,batch_row DESC) AS position
 FROM aabha_sql.raw_orders AS r
)
SELECT r.batch_row,r.source_order_id,lower(trim(r.status_text)) AS status,r.amount
FROM ranked AS r JOIN aabha_sql.customers AS c ON c.customer_id=r.customer_id
WHERE r.position=1 AND r.source_order_id IS NOT NULL AND r.amount>=0
 AND lower(trim(r.status_text)) IN ('completed','pending','cancelled')
 AND r.order_date_text IS NOT NULL AND pg_input_is_valid(r.order_date_text,'date')
ORDER BY r.source_order_id;

Expected output:

Runnable answer: sql/solutions/10b.sql