DT8: Advanced SQL for analysis
Traps that make numbers wrong
🎯You'll be able to
Spot and avoid the common SQL traps: join fan-out, NULL handling, NOT IN with NULLs, averages of averages and time-zone boundaries.
🏠The everyday picture
A shop's tally can go wrong in a few well-known ways: counting a bill twice, skipping a blank line, or adding percentages. Knowing the usual mistakes is half of avoiding them.
📘What it really is
-
Join fan-out: joining to a table with several matches per key copies rows. Joining SabziSathi's order lines to the area history on the customer key alone, without the time condition, turns 10,181 lines into 10,882 and inflates revenue. Check the row count after every join.
-
NULLs:
count(*)counts rows;count(col)skips NULLs;avg(col)averages only non-NULL values;x = NULLisn't true for any row; useis null;-
x not in (select y ...)returns no rows if anyyis NULL; usenot existsinstead. -
Averages of averages (DT6 Lesson 3): divide summed parts.
- Distinct counts across groups don't add up.
-
Time boundaries: group by local date, with time zones converted first (DT1 Lesson 4); use half-open ranges (
>= '2026-10-01' and < '2026-11-01') instead ofbetweenon timestamps. -
Integer division: in some databases
7 / 2is3. Cast to a decimal type first when you need fractions.
📝A worked example
Input: two checks in DuckDB:
select count(*), count(x), avg(x), sum(x) from (values (10), (null), (20)) t(x);
select count(*) from (values (1), (2)) a(x) where x not in (select * from (values (2), (null)) b(y));
Expected output: the first gives 3, 2, 15.0, 30: three rows, two values, and an average over the two values only. The second gives 0:
the NULL in the list makes not in return nothing, even for x = 1. With not exists, the answer would be 1.
🌍Where it's used
- Every analysis: these traps appear in reports of every kind.
- Code review: reviewers look for joins without row checks and
not insubqueries. - Data tests: uniqueness tests catch fan-out on every build (DT7).
📝Case scenario
A lender's report of customers "with no missed payment" used customer_id not in (select customer_id from missed_payments). One row in
missed_payments had a NULL customer id from a data entry error, and the report suddenly showed no customers at all. The team switched to
not exists and added a not_null test on the column.
🖱️Try it
In the solution, join fct_order_lines to dim_customer_area_history on customer_key only and count the rows, then add the time condition
from DT6 Lesson 4. Success: 10,882 rows, then 10,181.
⚠️Common mistakes
- No row check after a join. Sign: totals grow after "just adding a column". Fix: count before and after.
not inwith a subquery. Sign: empty results with no error. Fix:not exists.betweenon timestamps. Sign: the last day's late orders are missing, or midnight is counted twice. Fix: half-open ranges.
🛠️For your project
Project 2 test 2 checks the fact table keeps one row per order line, which catches fan-out from the history join.
✅Quick check
What happens to not in when the list contains a NULL?
Show the answer
It returns no rows.
How do you catch join fan-out?
Show the answer
Compare row counts before and after the join.