Data Quality Audit

This dataset is clean, and your job is to prove it. Each audit below returns counts of violations, and the contract is that every count is zero. That framing is exactly how production data tests work: the query encodes the rule, an empty (or all-zero) result is the pass, and the same query keeps guarding the warehouse as new rows arrive.

Three audits:

1. order headers reconcile with their line items
2. returns reference real order items and never exceed the line's value
3. no orphaned facts (items without orders, orders without customers)

Task: count orders whose header order_total disagrees with the sum of their line items by more than 0.01 (a tolerance for decimal rounding). Include every order, even cancelled ones, and treat an order with no line items at all as a mismatch (its line sum is 0, not NULL). Output a single row with one column, mismatched_orders.

The skill: aggregate lines to order grain first, then LEFT JOIN from headers so an order with zero lines cannot silently disappear from the audit.

Task: validate ecom_returns against ecom_order_items in one pass. Output a single row with two columns:

• orphan_returns: returns whose order_item_id does not exist in ecom_order_items
• over_refunded_returns: returns whose return_amount exceeds the matched line's line_total by more than 0.01

Both must be 0. A refund larger than the line it refunds is how negative net revenue sneaks into dashboards.

Task: check both ends of the order chain in one row:

• items_without_orders: line items whose order_id does not exist in ecom_orders
• orders_without_customers: orders whose customer_id does not exist in ecom_customers

Both must be 0. The anti-join shape (LEFT JOIN ... WHERE right_key IS NULL) is the same one you eyeballed on the exploration page. Now it carries a contract.

The data is proven clean, so now put it to work:

• eCommerce Graded Drills (Easy → Hard)