SQL Window Functions Cheatsheet

This deck is a focused walkthrough of SQL window functions, the set of features that let you compute values across rows related to the current one without collapsing them into a single output row. The cards walk through the most common patterns you'll reach for in real querying: running totals, rankings within groups, lag and lead lookups, moving averages, percentile ranks, and bucketing rows into N equal slices. You'll also untangle the subtle gotchas, like why a plain LAST_VALUE can quietly return the wrong answer and how a frame clause fixes it.

It's a good fit if you're an analyst, data engineer, or anyone writing SQL beyond basic SELECT and GROUP BY queries, and especially useful if you're preparing for a technical interview where window functions come up frequently. The concepts here tend to trip people up because window functions look like ordinary aggregates but behave differently: they keep row-level detail while still letting you compare across partitions. Working through the differences between ROW_NUMBER, RANK, and DENSE_RANK, or between a window aggregate and a grouped aggregate, will sharpen that intuition.

Because the cards are phrased as questions, try answering each one out loud or sketching the syntax on paper before flipping. When a card asks for a query, actually run it in a SQL playground if you can — muscle memory for the OVER (...) clause is worth more than rereading the answer. The topics build on each other, so if a later card about FIRST_VALUE or moving averages feels shaky, it's worth revisiting the earlier partitioning and ordering cards first. Spacing your review over a few days rather than cramming will help the patterns stick, since window function syntax rewards steady, repeated exposure more than a single long session.

A window function is computed across a set of rows related to the current row without collapsing the result set, which is the key distinction from a GROUP BY aggregation. Its general syntax follows the pattern function() OVER (PARTITION BY ... ORDER BY ... frame_clause), where PARTITION BY divides rows into independent groups analogous to a GROUP BY that does not collapse rows, and the ORDER BY inside the OVER clause defines the logical order within each partition used for ranking and frame evaluation. The frame clause then narrows which rows within the partition the function operates on, expressed using ROWS BETWEEN ... PRECEDING/FOLLOWING style bounds, and each window is applied independently within its partition.

Frames have important defaults that catch many beginners. When an OVER clause includes an ORDER BY, the default frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which yields a running total. Without an ORDER BY, the entire partition forms the frame, which is exactly what is needed for percent-of-total calculations. ROWS, RANGE, and GROUPS differ in subtle but important ways: ROWS counts physical row offsets, RANGE treats rows with the same ORDER BY value as a single logical position so peer rows share the same window endpoint, and GROUPS treats peer ties as units for frame definition. The optional EXCLUDE clause further refines frames with options like EXCLUDE CURRENT ROW, EXCLUDE GROUP, and EXCLUDE TIES, allowing fine-grained control over which rows participate.

Several behavioral rules deserve to be memorized. Windows are evaluated after WHERE but before the outer ORDER BY, so a window function cannot appear in a WHERE clause directly — the typical workaround is wrapping in a subquery, using a CTE, or employing QUALIFY where the engine supports it. The ORDER BY inside OVER is also independent of the query-level ORDER BY: the former orders the frame, while the latter orders the final result. Multiple windows sharing the same definition can be declared once via a named WINDOW clause, which improves readability and avoids repetition when computing several measures over the same partition and ordering.

The ranking family includes ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST. ROW_NUMBER assigns a unique sequential number to each row within a partition, making it the go-to choice when each row must receive a distinct position. RANK and DENSE_RANK both assign the same rank to tied values, but RANK leaves gaps afterward (so a tie at rank 1 followed by another tie produces ranks 1, 1, 3), while DENSE_RANK continues consecutively (1, 1, 2). Choosing between them hinges on whether gaps in ranking should propagate: DENSE_RANK is preferred for top-N lists where consecutive ranks carry meaning, while RANK is preferred when gaps communicate the existence of ties.

NTILE(N) distributes rows into N buckets as evenly as possible, returning bucket numbers from 1 to N; when the row count is not perfectly divisible, the first (rows mod N) buckets receive an extra row. PERCENT_RANK computes (rank - 1) / (rows - 1), giving a relative position between 0 and 1, while CUME_DIST returns the fraction of rows with values less than or equal to the current row's value, ranging from 0 to 1 inclusive. These two functions complement NTILE for percentile-based bucketing and serve slightly different analytical purposes, with PERCENT_RANK emphasizing relative position and CUME_DIST emphasizing cumulative mass.

The classic "top N per group" pattern relies on ROW_NUMBER inside a subquery, with rn <= N in the outer filter. When ties should be preserved across the boundary, RANK or DENSE_RANK should be used with a rank <= N filter instead, which includes all tied items rather than arbitrarily cutting them off. A subtle but critical detail is that the ORDER BY in the ranking window must include a tiebreaker such as a unique id or timestamp; without it, equal-keyed rows can shuffle between runs, producing non-deterministic results and unstable pagination. Pagination built on ROW_NUMBER inherits the same instability and is best replaced with keyset pagination using WHERE id > last_seen for large datasets, since OFFSET scans and discards skipped rows.

The value family — LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE — fetches values from other rows relative to the current one within the same partition. LAG(col, n, default) returns the value from n rows before the current row, with a default offset of 1 (the immediately previous row), and returns NULL or the supplied default when no such row exists in the partition. LEAD is its symmetric counterpart, looking forward in the ordering. Together they are the workhorses for computing differences between adjacent rows, year-over-year deltas via LAG(amount, 12) OVER (PARTITION BY metric ORDER BY month), percent change between consecutive prices, days since the previous order per customer, and similar adjacent-row comparisons.

FIRST_VALUE and LAST_VALUE pick from the boundaries of the partition or frame and share similar semantics. A notorious gotcha: plain LAST_VALUE(x) OVER (PARTITION BY g ORDER BY ts) returns the current row's value rather than the partition's last value, because the default frame ends at CURRENT ROW. The fix is to explicitly set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. LAST_VALUE combined with IGNORE NULLS and a cumulative frame is the standard trick for "carry forward" or "forward-fill" semantics, where missing values should be replaced by the most recent non-null value. NTH_VALUE(col, n) retrieves the n-th value in the frame, useful for picking a specific peer position such as the third-highest score.

NULL handling deserves special attention. The LAG(col, n, default) signature lets you supply a fallback for missing rows, but this is a positional, constant replacement. For more flexible NULL suppression when picking a value, IGNORE NULLS is available in PostgreSQL 16+, BigQuery, Snowflake, Redshift, SQL Server, and Oracle (with limited support in MySQL until 8.0.22), and skips NULLs when selecting a value, while RESPECT NULLS treats NULL as a valid value. When ordering within the window, appending NULLS FIRST or NULLS LAST controls where NULLs land in the frame, which matters for ranking positions, frame boundaries, and the behavior of cumulative aggregates when NULLs are present.

Standard aggregates — SUM, AVG, COUNT, MIN, MAX, and their statistical variants — become window functions when used with an OVER clause, returning one value per row without collapsing the result set. The most common applications are running totals via SUM(x) OVER (ORDER BY ts), cumulative averages via AVG(x) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING), and moving averages over a fixed-width trailing window such as the seven-day average AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Frame sizes are counted in physical rows with ROWS, and centered windows expressed as BETWEEN N PRECEDING AND N FOLLOWING include 2N+1 rows around the current row, useful for symmetric smoothing.

Comparing window aggregates to plain aggregates over the entire partition reveals several useful patterns. SUM(x) OVER () returns the grand total on every row, and dividing by it yields percent-of-total without a separate subquery. SUM(x) OVER (PARTITION BY g) returns the group total, supporting percent-of-group calculations. COUNT(*) OVER () returns total row count per row, which is convenient for pagination metadata in a single query without needing a second SELECT. Running maximum and minimum are achieved via MAX and MIN with ROWS UNBOUNDED PRECEDING, and these are handy for tracking all-time highs and lows within a partition up to the current row. Snowflake additionally offers RATIO_TO_REPORT(x) OVER (PARTITION BY g) as a built-in shortcut for the x-over-group-total calculation.

Conditional aggregation in windows is supported via the FILTER clause, for example SUM(x) FILTER (WHERE active) OVER (...), which is cleaner than wrapping in CASE and may help the optimizer prune work. Statistical aggregates including STDDEV_SAMP, STDDEV_POP, VAR_SAMP, VAR_POP, and even CORR plus the REGR_SLOPE and REGR_INTERCEPT regression functions are usable as window functions. A practical limitation: window functions do not support DISTINCT directly, so cumulative distinct counts require a workaround such as windowing over a first-occurrence flag (where ROW_NUMBER() = 1 acts as 1/0) and summing that flag across the frame. The same pattern applies to rolling unique-user counts over time-based windows.

Window functions unlock a remarkable range of analytical patterns. Sessionization — splitting a user's event stream into sessions based on a 30-minute inactivity gap — is achieved by summing a flag (1 when the gap from LAG exceeds 30 minutes, else 0) over the partition ordered by timestamp, which increments a session_id at each new session. Gaps and islands problems, where consecutive dates form a streak that should be grouped together, are solved by subtracting ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' from the date — rows in the same streak produce the same result. Cohort retention combines window aggregations with grouping, normalizing COUNT(DISTINCT user_id) over cohort and period by the first-period count retrieved via FIRST_VALUE.

Statistical measures are easily computed per partition. Z-scores use (x - AVG(x) OVER (...)) / STDDEV(x) OVER (...), with NULLIF guarding against zero standard deviation. Coefficient of variation divides STDDEV by AVG within the partition. Median per group uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY g) in PostgreSQL and BigQuery; SQL Server requires an OVER clause even for whole-table percentiles. Mode per group is found by aggregating counts per value, then ranking by count with ROW_NUMBER. Outlier detection via IQR computes Q1 and Q3 with PERCENTILE_CONT and flags rows beyond 1.5*IQR from the quartiles, while 3-sigma price spike detection compares current values to AVG ± 3*STDDEV over a trailing window.

Some calculations resist pure window expressions. Exponential moving averages require a recursive CTE because each EMA value depends on the previously computed EMA, with the recursive shape anchoring the first row and applying 0.3 * t.x + 0.7 * e.ema for subsequent rows. Cumulative distinct counts are likewise not directly expressible and need the first-occurrence-flag workaround. Time-weighted averages combine LAG on timestamps for interval lengths multiplied by values and summed, which is feasible but non-trivial. Sliding range frames with multiple intervals in one query are not supported, requiring either separate window calculations or self-joins on shifted timestamps. Lateral joins fill the gap when "the previous row matching a complex condition" cannot be expressed as a fixed offset, joining each row to a per-row subquery that selects the desired match.

Performance hinges on a few guiding principles. Composite indexes on the partition and order columns let the engine avoid a Sort step — EXPLAIN plans in PostgreSQL show a WindowAgg operator without an upstream Sort when the index supports the window. CTE materialization can be forced with WITH x AS MATERIALIZED (...) in PostgreSQL 12+, which changes whether the planner can inline the CTE and may either help or hurt performance. Windows over joined data often force materialization of the join, so windowing first in a CTE and then joining is frequently faster than computing windows after the join. Large partitions can spill to disk because each partition must fit in memory for the sort and buffer; raising work_mem in PostgreSQL, clustering Snowflake tables on partition keys, or partitioning and clustering BigQuery tables by date and partition columns all help.

Engine support varies meaningfully and should drive query portability choices. QUALIFY, which filters on window functions without subquery nesting, is supported in Snowflake, BigQuery, Databricks, and Teradata but not natively in PostgreSQL, MySQL, or SQL Server. RANGE with INTERVAL frames for rolling time-based metrics is supported in PostgreSQL, BigQuery, and DuckDB but not in older MySQL or SQL Server, which require ROWS over pre-aggregated daily counts. MySQL gained window function support only in 8.0; SQLite added it in 3.25 in 2018; ClickHouse has supported windows since 21.x with some unique syntax; DuckDB offers the richest open-source support including QUALIFY and RANGE intervals. PostgreSQL's DISTINCT ON (g) ORDER BY g, ts DESC is a concise shortcut for keeping first-per-group without an explicit ROW_NUMBER subquery.

Several common mistakes are worth flagging. Filtering rows with a window function inside WHERE is invalid — the fix is QUALIFY, a CTE or subquery, or DISTINCT ON in PostgreSQL. Forgetting a tiebreaker in the ORDER BY of a ranking window produces non-deterministic results, so a unique id or sequence should always be appended. Wrapping a UNION ALL in a subquery is required before applying a window over the combined result, otherwise the parser may error or scope unexpectedly. Updating or deleting rows using a window function follows the pattern of computing the window in a subquery and joining back to the target table, useful for deduplication and backfills. For data engineering workflows, DBT models in the mart layer commonly use window functions for ranking, deduplication, and retention metrics, and Spark SQL uses the same syntax via the Window API for distributed execution with partition-based parallelism.