Find the wrong number in a chain store's sales report

The situation

A shoe retailer with branches in Lahore, Karachi and Faisalabad reports monthly revenue from a SQL query an intern wrote two years ago. The finance manager suspects the figures are wrong, because the Karachi number jumped when a second delivery address was added for some customers.

Rebuild the report so that every number can be defended, explain where the old one went wrong, and make it fast and safe to run.

What you'll be able to show

• Detect silent row loss and silent row multiplication in joins
• Use window functions for rankings, running totals and month-on-month change
• Restructure a nested query into named CTE steps a colleague can read
• Read a query plan, add an index, and prove the change

What you need

• SQLite (DB Browser for SQLite), MySQL or PostgreSQL; any one is fine
• The four tables you will create below, with invented but realistic data

Tasks

1. 1Build the data, with the trap in it

   Create customers (id, name, city), addresses (customer_id, address), orders (id, customer_id, branch, order_date, amount) and products. Insert at least 40 orders over three months. Give five customers two addresses, and leave three orders pointing at a customer id that does not exist.
   A correct result: Row counts for each table, and a note naming which five customers have two addresses and which three orders are orphans.

2. 2Reproduce the wrong report

   Write the intern's query: orders INNER JOIN customers INNER JOIN addresses, SUM(amount) by branch and month. Then write the correct total from orders alone.
   A correct result: Two sets of totals side by side, and the difference explained: which rows were multiplied and which were lost.

3. 3Fix it and prove it

   Rewrite the report so every order is counted exactly once: LEFT JOIN where you are counting orders, and never join to a table whose key is not unique for the grain you report at. Check row counts before and after each join.
   A correct result: Branch-month totals that match SUM(amount) from orders exactly, with the before-and-after row counts written beside each join.

4. 4Rank and compare with windows

   Using window functions: the top three customers by revenue in each branch, a running total of revenue by month for each branch, and each month's change from the previous month (LAG).
   A correct result: Three result sets, each produced by one query with a window clause and no self join.

5. 5Make it readable

   Rewrite the final report as a chain of named CTEs (for example clean_orders, monthly_branch, ranked). Add one comment line above each step saying what business rule it applies.
   A correct result: A query a colleague can read top to bottom, with no step called t1 or x.

6. 6Make it fast and safe

   Run the plan (EXPLAIN or EXPLAIN QUERY PLAN) on the report, add an index on orders(customer_id) or orders(branch, order_date), and run it again. Then write the one-off correction for the three orphan orders as a SELECT first, then inside a transaction.
   A correct result: Both plans with the change named, and the SELECT that preceded the UPDATE or DELETE, wrapped in a transaction.

What to hand in

One .sql file with every statement in order and comments, plus a one-page note to the finance manager: what was wrong, by how much, and how the new report prevents it.

How it is marked