Analytics & reporting
24 steps in 5 sets of SQL.
The tools for questions that do not fit in a single flat query. Subqueries, then common table expressions including recursive ones, then window functions.
Window functions are the centerpiece and the thing most people learn last and wish they had learned first: ranking, running totals, lag and lead, and frames. Twenty-four steps.
Subqueries
- Subquery in WHEREA subquery computes the list of values the outer filter matches against.
- Scalar subqueryA subquery that returns one value can stand in for a column or a constant.
- EXISTS and NOT EXISTSEXISTS asks whether any matching row exists, and stops at the first one.
Common table expressions
- Common table expressionWITH names a result set up front so the main query reads like prose.
- Chained CTEsSeveral CTEs, comma separated, each free to build on the one before.
- Recursive CTEA recursive CTE walks a hierarchy or generates a series.
Window functions
- Ranking within a partitionWindow functions rank rows without collapsing them into groups.
- RANK, DENSE_RANK, ROW_NUMBERThree ways to number rows, and they disagree exactly when there is a tie.
- Running totalsAn ordered window turns a column of amounts into a cumulative curve.
- LAG and LEADLAG looks back a row, LEAD looks forward — growth rates without a self join.
- Window framesThe frame decides which rows the window function can see from where it stands.
- Naming a windowRepeating the same OVER clause four times is how a typo gets in.
- FIRST_VALUE and LAST_VALUEReaching for the edges of a partition without a second query.
- Buckets with NTILENTILE splits ordered rows into equal-sized buckets — quartiles, deciles, cohorts.
- Where a row sits in the distributionNot the rank but the fraction: how much of the table is behind this row?
Expressions
- CASE expressionsCASE is SQL's conditional: the first matching branch wins.
- COALESCE and NULLIFTwo small tools for missing data: pick the first non-NULL, or make a value NULL.
- FILTER inside an aggregateOne pass over the table, several differently filtered counts.
- Working on textThe string toolkit: measure, slice, find, replace, tidy.
- Numbers and roundingRounding is a decision, so make it an explicit one.
- Dates and timesTruncating, formatting and shifting timestamps to group by period.
- Date arithmeticDates are text in SQLite, and the modifiers do the calendar work for you.
Encore
- cohort_report.sqlSignup cohorts and the share of each still ordering months later.
- revenue_dashboard.sqlMonth over month, year to date, and each month's share of the best month so far.