#127 SQL and analytics 6.6 min
SQL Mistakes That Give Wrong Numbers
Six made-up orders total 220, but two joins make the same SUM say 505, with no error.
Run all four checks
Copy the code folder first. Run python run.py fanout, python run.py nulls avg and python run.py divide. For each line, write down which number is the correct one and which is the mistake, using the six orders in shop.py.
A hint
The orders table has six rows, and its amounts add up to 220.
Fill in the missing statuses
In shop.py, order 4 and order 6 have status None. Change order 4 to 'paid' and order 6 to 'cancelled'. Predict how the status <> cancelled and status IS NOT cancelled lines change in python run.py nulls, and what python run.py divide prints. On this machine both nulls lines give 4 orders and 140, and the divide share is 33 for 100 * bad / n and 33.3 for the last one.
A hint
Once no status is null, the two filters ask the same question.
Take one box away
In shop.py, remove the box B entry from SHIPMENTS, so order 1 ships in only one box. Predict the row count and total in the join items and boxes line of python run.py fanout. On this machine it now prints 10 | 425, the same as join items.
A hint
Each order now has at most one shipment, so the second join no longer multiplies rows.
Give everyone a discount
In shop.py, give order 2 a discount of 4 instead of None. Predict the two lines of python run.py avg before you run it. On this machine avg(discount) prints 4.75 and the null as zero version prints about 3.1666666666666665. Then explain why the two numbers are still different.
A hint
Two orders still have a null discount. Average divides by the number of non null values.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Six made-up orders run only in SQLite; PostgreSQL's IS DISTINCT FROM is mentioned from its docs, not run.
