sql

SQL

Analytics SQL, written to be read and to be run. Every query answers a question someone would actually ask in an interview or a stand-up, and every one comes with the reasoning: what the naive version gets wrong, and why this version is right.

Running

No setup, no server, no dependencies — sqlite3 ships with Python.

python sql/run.py 03_cohort_retention.sql

python sql/run.py --all

run.py builds a fresh in-memory database from schema.sql, runs the query, and prints the result. The fixture is deliberately tiny (12 users, 20 orders, 28 events) so every answer can be checked by hand — the cohort matrix, the funnel counts and the session boundaries are all verifiable by reading the INSERTs.

The queries

#	Query	The idea it exists to demonstrate
01	Running totals and frames	What a window frame is, and why ROWS ≠ RANGE
02	Top N per group	ROW_NUMBER vs RANK vs DENSE_RANK; why LIMIT can't do this
03	Cohort retention	Relative month indexing and a fixed denominator
04	Funnel conversion	Steps declared, not discovered — so an empty step stays visible
05	Sessionization	Gaps and islands: a cumulative sum over boundary flags is the group id
06	Day-over-day and moving average	LAG, trailing frames, and the incomplete-window trap
07	Median and percentiles	Median without PERCENTILE_CONT; why the mean misleads on skewed data
08	As-of join	Point-in-time correctness — the most expensive silent bug in analytics
09	Data-quality checks	Orphans, duplicates, guardrails — as one alertable result set
10	Recursive calendar spine	Recursive CTEs, and why missing days corrupt every trend

Queries 06 and 10 are meant to be read in order: 06 leaves a real flaw in place (days with no orders simply vanish), and 10 is the fix.

Portability

Everything sticks to standard SQL — CTEs, window functions, LEFT JOIN, UNION ALL — so the queries move to Postgres, DuckDB or BigQuery with at most a date-function rename. The SQLite-specific parts are called out in the comments where they appear: strftime for date parts, and julianday for time differences, since SQLite has no interval type.