Cheat sheet · Data & MLOps
SQL for data science cheat sheet
The key ideas on one page — plus common pitfalls and interview tips. Skim it before an interview, or print it for revision.
Cheat sheet
A dense, interview-grade reference covering query logical order, joins and NULL semantics, aggregation and pivoting, window functions, CTEs/subqueries/recursion, performance tuning, and the analytics patterns (cohorts, sessionization, gaps-and-islands) that data scientists and ML engineers are expected to write fluently in 2026.
Query Execution & Logical Order
- Logical processing order (NOT the written order): FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET. Window functions are evaluated after HAVING but before DISTINCT/ORDER BY, which is why you cannot put a window function in WHERE or GROUP BY.
- WHERE filters rows BEFORE aggregation; HAVING filters groups AFTER aggregation. Use WHERE for raw-row predicates (sargable, indexable) and reserve HAVING for conditions on aggregates.
- Standard SQL: SELECT aliases are NOT visible in WHERE, GROUP BY, or HAVING (those run before SELECT projection); they ARE visible in ORDER BY. Many engines (Postgres, MySQL) permissively allow an alias in GROUP BY/HAVING as an extension, but do not rely on it for portable code -- wrap the computed column in a subquery/CTE instead.
- DISTINCT runs after SELECT and is effectively a GROUP BY on all output columns; use it only when you truly need dedup, not to paper over a fan-out join.
- LIMIT/TOP without ORDER BY is non-deterministic -- the engine may return any rows; always pair it with a deterministic ORDER BY that includes a unique tiebreaker key.
Joins & NULL Semantics
- INNER returns matches only; LEFT keeps all left rows (NULLs where no match); FULL keeps both sides; CROSS is the Cartesian product. Choose LEFT when you must preserve the driving table's row count.
- Filtering a LEFT-joined (right-side) column in WHERE silently demotes the join to an INNER join, because NULLs from unmatched rows fail the predicate. Put right-table conditions in the ON clause to keep unmatched left rows.
- Fan-out: a one-to-many join multiplies rows and inflates SUM/COUNT. Pre-aggregate the many-side in a CTE before joining, or use COUNT(DISTINCT key).
- NULL means unknown, not zero: NULL = NULL evaluates to NULL (not true), so use IS NULL / IS NOT DISTINCT FROM for NULL-safe equality. NOT IN with any NULL in the subquery returns no rows -- prefer NOT EXISTS.
- COUNT(*) counts rows; COUNT(col) skips NULLs; SUM/AVG/MIN/MAX ignore NULLs (so AVG(col) divides by the non-NULL count, not the row count -- COALESCE to 0 first if you want NULLs counted).
- Anti-join (rows with no match): LEFT JOIN ... WHERE r.id IS NULL, or NOT EXISTS. Semi-join (existence only, no fan-out): EXISTS or IN.
Window Functions (the DS workhorse)
- Window functions compute across a set of rows WITHOUT collapsing them (unlike GROUP BY). Syntax: func() OVER (PARTITION BY ... ORDER BY ... frame).
- Ranking: ROW_NUMBER (unique, breaks ties arbitrarily), RANK (gaps after ties), DENSE_RANK (no gaps), NTILE(n) for equal-size buckets/deciles. Use ROW_NUMBER for top-N-per-group and dedup.
- Offset/navigation: LAG/LEAD for period-over-period deltas; FIRST_VALUE/LAST_VALUE/NTH_VALUE for baselines -- useful for retention, churn, and time-series features.
- Frames: ROWS is physical-row based (use for moving averages); RANGE/GROUPS are value/peer based. With ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- the classic LAST_VALUE gotcha (it returns the current row, not the partition's last). Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.
- Running aggregates: SUM(x) OVER (ORDER BY ts) gives a cumulative total (default frame is to CURRENT ROW); ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a 7-row moving average.
- QUALIFY (Snowflake, BigQuery, DuckDB, Databricks, Teradata) filters on window-function results without a wrapping subquery -- cleaner than the CTE/subquery + WHERE rn = 1 pattern (which is the portable fallback).
CTEs, Subqueries & Recursion
- CTEs (WITH) improve readability and let you reference a result multiple times; chain them for multi-step pipelines instead of deeply nested subqueries.
- Correlated subqueries reference the outer query and conceptually re-run per outer row (can be O(n*m)); rewrite as a JOIN or window function when possible for performance.
- Materialization varies by engine: Postgres historically treated CTEs as an optimization fence (always materialized); since v12 it inlines single-reference, side-effect-free CTEs by default unless you write WITH ... AS MATERIALIZED. Know your engine.
- Recursive CTEs (WITH RECURSIVE) traverse hierarchies/graphs (org charts, bill-of-materials, shortest paths). Always include a termination condition or depth guard to avoid runaway recursion.
- Prefer EXISTS over IN for existence checks (NULL-safe and often optimized identically); prefer a JOIN over a correlated subquery when pulling columns from another table.
- Lateral joins (LATERAL in Postgres/MySQL, CROSS/OUTER APPLY in SQL Server) let a subquery reference columns from preceding FROM items -- ideal for top-N-per-group with extra columns.
Aggregation, Pivoting & Analytics Patterns
- Conditional aggregation -- SUM/COUNT(CASE WHEN ...) -- pivots long->wide and computes filtered metrics in one pass, far cheaper than multiple self-joins. Postgres/standard SQL FILTER (WHERE ...) is the cleaner equivalent.
- GROUPING SETS / ROLLUP / CUBE produce multi-level subtotals (e.g. totals by region plus a grand total) in a single query; use GROUPING() to distinguish a genuine NULL from a subtotal row.
- Cohort/retention: bucket users by signup period, then COUNT(DISTINCT user_id) active per period offset; date_trunc / date_diff drive the bucketing.
- Sessionization (gaps-and-islands): order events per user, flag a new session when the gap since the previous event exceeds a threshold via LAG, then take a running SUM of the flags as the session id.
- Median/percentiles: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) interpolates between rows; PERCENTILE_DISC returns an actual data value. AVG is not robust to skew -- report medians/percentiles for revenue and latency.
- Deduplication for ML feature tables: ROW_NUMBER per entity key with rn = 1, or pick the canonical record with MAX_BY/ARG_MAX (Postgres: DISTINCT ON (key) ... ORDER BY key, ts DESC).
Performance & Optimization
- Read EXPLAIN / EXPLAIN ANALYZE: watch for Seq Scan on large tables, Nested Loops with huge row counts, and large gaps between estimated and actual rows (stale stats -> run ANALYZE / refresh statistics).
- Sargability: keep the indexed column bare on one side of the predicate. WHERE func(col) = x, col + 1 = y, or leading-wildcard LIKE '%x' defeat B-tree indexes; rewrite, or add an expression/partial index.
- Indexing: B-tree for equality/range; composite index column order matters (leftmost-prefix rule); covering indexes (INCLUDE) avoid heap lookups. Over-indexing slows writes.
- SELECT * forces wide reads and breaks covering indexes -- project only the columns you need. Filter and aggregate early; push predicates down before joins.
- Partition pruning and clustering (BigQuery partition + cluster, Snowflake micro-partitions) cut scanned bytes -- columnar warehouses bill on data scanned, so column projection and partition filters are direct cost savings.
- For huge joins/dedups, beware hash spills to disk. Paginate with keyset/seek (WHERE id > :last_id) instead of large OFFSET, which still scans and discards every skipped row.