The Practical Data Analyst

Part 1 · 8 min

Window Functions Are SQL's Most Underused Feature. Five Patterns Cover Nearly Everything.

Five window-function patterns that cover most analytics SQL: dedupe with ROW_NUMBER, top-N per group with RANK, period comparisons with LAG, running totals with explicit frames, bucketing with NTILE. Every query ran and its output is shown; a 200,000-row timing test puts the window function at 0.46s against 5.92s for the equivalent self-join on 5,000 rows.

Part 2 · 7 min

Data Storytelling Teaches You to Present a Finding. Nobody Teaches You to Find One.

Every guide teaches you to present a finding. None teach you to find one. The four questions that do the work, including the one that broke open Google's Sierra Leone trial.

Part 3 · 8 min

Most Analytics Bugs Are Data Bugs. Five Checks That Catch Them Before the Meeting.

Five validation checks every analyst should run before trusting a table: duplicates, NULL key columns, out-of-range values, cross-field arithmetic, orphan references. Tested on a 2,010-row orders table with 50 planted defects; the checks flagged 51 rows, and the misses are as instructive as the catches.

Part 4 · 8 min

Five String Traps That Rewrite Your Totals. A 12-Row Table Lost $415 Before a Single Clean.

A raw join on city names matched 8 of 12 rows and lost $415 of $1,420. GROUP BY invented four extra cities. COUNT said 11 emails existed; only 10 were real. Five traps, all measured.

Part 5 · 10 min

Five CTE Traps That Return the Wrong Answer Quietly

Five CTE traps, each measured on one 12-row SQLite table: a random value that ran once, a recursion with no end, a $235 filter-order gap, a table name stolen by its own CTE, and a UNION that ate a path.

Part 6 · 7 min

Five JOIN Traps That Quietly Rewrite Your Numbers. One Orders Table Reported $620, $1,240 and $2,040.

A 6-order table holding $620 in revenue produced totals of $1,240 and $2,040 after two ordinary joins, then lost $70 to a third. Five JOIN traps, each shown on real output, each with a fix.

Part 7 · 7 min

Your Query Is Slow. EXPLAIN Told You Why: Five Plan Checks That Catch It Before Users Do

Five EXPLAIN QUERY PLAN checks on 100,000 SQLite rows: SCAN versus SEARCH, a covering index, a join plan, a temp sort, and a LIKE trap where the index did not rescue the query at all.

Part 8 · 10 min

Five Date Traps That Move Money Between Months: I Measured Each One

Five controlled date experiments on a tiny orders table: a BETWEEN filter cut March from $420 to $170, a timezone moved $70 into February, and SQLite and pandas disagreed by three days on one month of arithmetic.

Part 9 · 10 min

Five Aggregation Traps That Rewrite Your Totals. I Measured Each One on Twelve Rows.

Five aggregation traps measured on one 12-row sales table in SQLite 3.45.1: integer division, averages of averages, COUNT(*) vs COUNT(column), the NULL group, and WHERE vs HAVING.