Short answer
A SQL window functions cheat sheet starts with one idea: a window function computes a value per row without collapsing rows like GROUP BY. Write function() OVER (PARTITION BY ... ORDER BY ... frame); all window functions share that clause.
On this page
- What is the syntax of a SQL window function?
- Which ranking function should you use?
- How do LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE work?
- How do you calculate a running total and a moving average?
- What is the difference between ROWS, RANGE and GROUPS?
- How do you use aggregate functions as window functions?
- How do you filter on a window function with QUALIFY?
- How do the databases differ on window functions?
- How do you calculate support resolution time in SQL?
- Where should you practice next?
This SQL window functions cheat sheet covers the syntax, the ranking and offset functions, frames, running totals, moving averages and QUALIFY. Every example runs on one eight-row table, and the output tables were produced by running the queries in SQLite 3.53. The last section shows which of seven databases supports what, from each vendor’s own docs. If you are new to SQL, start with the learn SQL hub.
The short version
- A window function keeps every input row.
GROUP BYcollapses rows;OVER (...)does not. - The default frame with
ORDER BYisRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWin PostgreSQL, MySQL 8, SQL Server and BigQuery, which makes tied rows share a running total. LAST_VALUEreturns the current row unless you widen the frame. Snowflake documents a whole-window default for it instead.GROUPSframes work in PostgreSQL 11+, SQLite 3.28+ and DuckDB. MySQL 8, SQL Server, Snowflake and BigQuery document onlyROWSandRANGE.QUALIFYexists in Snowflake, BigQuery and DuckDB. SQL Server has it only in Fabric Data Warehouse.IGNORE NULLSworks in DuckDB, Snowflake and SQL Server 2022+. PostgreSQL and MySQL 8 always respect nulls.- On the ticket table below, the three solved tickets took 330, 3,250 and 25 minutes, an average of 1,201.7 minutes.
What is the syntax of a SQL window function?
A window function is a function followed by OVER. Inside the parentheses you choose three things: how to split rows into partitions, how to order rows inside each one, and which rows around the current row count (the frame).
function_name(args) OVER (
[PARTITION BY col1, col2]
[ORDER BY col3 [ASC | DESC]]
[ROWS | RANGE | GROUPS BETWEEN frame_start AND frame_end]
)
All three parts are optional. With an empty OVER (), the whole result set is one window. PARTITION BY restarts the calculation for each group. ORDER BY sets the sequence the function sees. PostgreSQL documents the frame start options as UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING and UNBOUNDED FOLLOWING (PostgreSQL docs).
The toy table for every example is sales, with two regions and two deliberate ties (250 in East, 300 in West):
| rep | region | sale_date | amount |
|---|---|---|---|
| Ana | East | 2026-03-01 | 100 |
| Ben | East | 2026-03-02 | 250 |
| Cy | East | 2026-03-03 | 250 |
| Dee | East | 2026-03-04 | 400 |
| Eli | West | 2026-03-01 | 300 |
| Fay | West | 2026-03-02 | 100 |
| Gus | West | 2026-03-03 | 300 |
| Hal | West | 2026-03-04 | 200 |
Window functions run late in a query, after WHERE, GROUP BY and HAVING. That is why you cannot filter on them in WHERE. Our explainer on the SQL order of execution shows where they sit.
Which ranking function should you use?
Use ROW_NUMBER for a unique sequence, RANK when ties should share a rank and leave gaps, DENSE_RANK when ties should share a rank without gaps, and NTILE(n) to cut a partition into n buckets.
SELECT region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drnk,
NTILE(2) OVER (PARTITION BY region ORDER BY amount DESC) AS half
FROM sales;
| region | rep | amount | rn | rnk | drnk | half |
|---|---|---|---|---|---|---|
| East | Dee | 400 | 1 | 1 | 1 | 1 |
| East | Ben | 250 | 2 | 2 | 2 | 1 |
| East | Cy | 250 | 3 | 2 | 2 | 2 |
| East | Ana | 100 | 4 | 4 | 3 | 2 |
| West | Eli | 300 | 1 | 1 | 1 | 1 |
| West | Gus | 300 | 2 | 1 | 1 | 1 |
| West | Hal | 200 | 3 | 3 | 2 | 2 |
| West | Fay | 100 | 4 | 4 | 3 | 2 |
Ben and Cy tie at 250. RANK gives both 2 and skips 3. DENSE_RANK gives both 2 and continues at 3. ROW_NUMBER picks an arbitrary order between them, so add a tie-breaker column to ORDER BY when the result must be repeatable. MySQL’s docs also say ranking functions should be used with ORDER BY, because without it all rows are peers and numbering is nondeterministic (MySQL 8.0).
PostgreSQL, MySQL 8, SQLite and Snowflake also list PERCENT_RANK and CUME_DIST in their docs.
How do LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE work?
LAG(x, n) reads x from n rows before the current row, and LEAD(x, n) reads n rows after. The offset defaults to 1 and the fallback value defaults to NULL. FIRST_VALUE, LAST_VALUE and NTH_VALUE(x, n) read from the frame.
SELECT region, sale_date, amount,
LAG(amount) OVER w AS prev_amount,
LEAD(amount) OVER w AS next_amount,
FIRST_VALUE(amount) OVER w AS first_amt,
LAST_VALUE(amount) OVER w AS last_default,
LAST_VALUE(amount) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full,
NTH_VALUE(amount, 2) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amt
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date);
| region | sale_date | amount | prev_amount | next_amount | first_amt | last_default | last_full | second_amt |
|---|---|---|---|---|---|---|---|---|
| East | 2026-03-01 | 100 | NULL | 250 | 100 | 100 | 400 | 250 |
| East | 2026-03-02 | 250 | 100 | 250 | 100 | 250 | 400 | 250 |
| East | 2026-03-03 | 250 | 250 | 400 | 100 | 250 | 400 | 250 |
| East | 2026-03-04 | 400 | 250 | NULL | 100 | 400 | 400 | 250 |
| West | 2026-03-01 | 300 | NULL | 100 | 300 | 300 | 200 | 100 |
| West | 2026-03-02 | 100 | 300 | 300 | 300 | 100 | 200 | 100 |
| West | 2026-03-03 | 300 | 100 | 200 | 300 | 300 | 200 | 100 |
| West | 2026-03-04 | 200 | 300 | NULL | 300 | 200 | 200 | 100 |
The last_default column is the classic trap. With ORDER BY and no frame, the frame ends at the current row, so LAST_VALUE just repeats amount. last_full widens the frame and returns the real last value. SQLite’s docs note that most built-in window functions ignore the frame, with first_value, last_value and nth_value as the exceptions (SQLite).
Snowflake's default differs
Snowflake documents that FIRST_VALUE, LAST_VALUE and NTH_VALUE default to the entire window, not to the current row, and says this does not follow the ANSI standard. It recommends writing the frame explicitly (Snowflake docs). The last_default column above would match last_full there. Write the frame and the query means the same everywhere.
How do you calculate a running total and a moving average?
A running total is SUM with a frame from the partition start to the current row. A moving average is AVG with a frame of the last n rows. Write the frame as ROWS so the result does not depend on ties.
SELECT region, sale_date, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
ROUND(AVG(amount) OVER (PARTITION BY region ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3
FROM sales;
| region | sale_date | amount | running_total | avg_3 |
|---|---|---|---|---|
| East | 2026-03-01 | 100 | 100 | 100 |
| East | 2026-03-02 | 250 | 350 | 175 |
| East | 2026-03-03 | 250 | 600 | 200 |
| East | 2026-03-04 | 400 | 1000 | 300 |
| West | 2026-03-01 | 300 | 300 | 300 |
| West | 2026-03-02 | 100 | 400 | 200 |
| West | 2026-03-03 | 300 | 700 | 233.3 |
| West | 2026-03-04 | 200 | 900 | 200 |
The first rows of each moving average average fewer than three values, because the frame cannot reach past the partition start. BigQuery’s docs say the same: if a boundary extends past the partition, the frame includes only rows inside it (BigQuery docs).
What is the difference between ROWS, RANGE and GROUPS?
ROWS counts physical rows. RANGE counts by value of the ORDER BY column, so tied rows (peers) are included together. GROUPS counts whole groups of peers. The default frame is a RANGE frame, and that is where running totals go wrong on ties.
Order East by amount instead of date, so Ben and Cy tie:
SELECT rep, amount,
SUM(amount) OVER (ORDER BY amount) AS default_frame,
SUM(amount) OVER (ORDER BY amount
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM sales WHERE region = 'East';
| rep | amount | default_frame | rows_frame |
|---|---|---|---|
| Ana | 100 | 100 | 100 |
| Ben | 250 | 600 | 350 |
| Cy | 250 | 600 | 600 |
| Dee | 400 | 1000 | 1000 |
Both 250 rows see 600 under the default frame, because each includes its peer. ROWS adds one row at a time. Pick the one you mean. PostgreSQL, MySQL 8, SQL Server and BigQuery all document the RANGE ... UNBOUNDED PRECEDING AND CURRENT ROW default when ORDER BY is present.
GROUPS is rarer. This query sums the current group of equal amounts and the one group before it:
SELECT rep, amount,
SUM(amount) OVER (ORDER BY amount GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_two_groups
FROM sales;
| rep | amount | sum_two_groups |
|---|---|---|
| Ana | 100 | 200 |
| Fay | 100 | 200 |
| Hal | 200 | 400 |
| Ben | 250 | 700 |
| Cy | 250 | 700 |
| Eli | 300 | 1100 |
| Gus | 300 | 1100 |
| Dee | 400 | 1000 |
Hal’s 400 is the 200 group plus the 100 group (100 + 100 + 200). PostgreSQL added GROUPS mode and frame exclusion in version 11 (release notes), SQLite added them in 3.28.0 on 2019-04-16, and DuckDB documents all three modes. Two limits: SQL Server rejects RANGE with a numeric offset (Microsoft Learn), and BigQuery’s RANGE offsets need exactly one numeric ORDER BY key.
How do you use aggregate functions as window functions?
Put OVER (PARTITION BY ...) after SUM, AVG, COUNT, MIN or MAX. Each row keeps its own columns and gains the group’s aggregate, which makes percent-of-total simple. MySQL documents that most aggregate functions can be used this way, and DuckDB says all of them can.
SELECT region, rep, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales;
| region | rep | amount | region_total | pct_of_region |
|---|---|---|---|---|
| East | Ana | 100 | 1000 | 10 |
| East | Ben | 250 | 1000 | 25 |
| East | Cy | 250 | 1000 | 25 |
| East | Dee | 400 | 1000 | 40 |
| West | Eli | 300 | 900 | 33.3 |
| West | Fay | 100 | 900 | 11.1 |
| West | Gus | 300 | 900 | 33.3 |
| West | Hal | 200 | 900 | 22.2 |
SQLite’s docs add a restriction: window functions cannot use DISTINCT. SQL Server also states you cannot use OVER with DISTINCT aggregations. Count distinct values in a subquery first, then join the result back.
How do you filter on a window function with QUALIFY?
QUALIFY filters rows after window functions are computed, the way HAVING filters after GROUP BY. Snowflake, BigQuery and DuckDB support it. Everywhere else, compute the window in a subquery or CTE and filter outside.
Top two sales per region, with QUALIFY:
SELECT region, rep, amount
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) <= 2;
The portable version works in all seven databases:
SELECT region, rep, amount
FROM (
SELECT region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS rn
FROM sales
) t
WHERE rn <= 2;
| region | rep | amount |
|---|---|---|
| East | Dee | 400 |
| East | Ben | 250 |
| West | Eli | 300 |
| West | Gus | 300 |
I ran the portable version in SQLite. The QUALIFY form is a documented feature of the three engines above, not something we executed. Snowflake’s docs show QUALIFY after HAVING and require at least one window function in the query (Snowflake). BigQuery requires a window function in QUALIFY or the SELECT list (BigQuery). DuckDB says QUALIFY avoids a subquery or WITH clause and accepts a SELECT alias (DuckDB). In Microsoft’s docs, the QUALIFY clause is marked “Fabric Data Warehouse only”, so SQL Server and Azure SQL still need the subquery (Microsoft Learn).
How do the databases differ on window functions?
The core ranking, offset and aggregate functions work everywhere here. The differences are in frames, null handling, named windows and QUALIFY.
| Database | Frame units | GROUPS | IGNORE NULLS | Named WINDOW | QUALIFY |
|---|---|---|---|---|---|
| PostgreSQL | ROWS, RANGE, GROUPS | Yes (11+) | No, always RESPECT NULLS | Yes | No |
| MySQL 8.0 | ROWS, RANGE | No | Parsed, but errors | Yes | No |
| SQL Server | ROWS, RANGE (no RANGE offsets) | No | Yes (2022+) | Yes (2022+, compat level 160) | Fabric Data Warehouse only |
| Snowflake | ROWS, RANGE | Not documented | Yes (e.g. LAG) | Not confirmed in the docs we opened | Yes |
| BigQuery | ROWS, RANGE | Not documented | FIRST_VALUE, LAST_VALUE, NTH_VALUE only | Yes | Yes |
| SQLite | ROWS, RANGE, GROUPS | Yes (3.28+) | Not documented | Yes | No |
| DuckDB | ROWS, RANGE, GROUPS | Yes | Yes | Yes | Yes |
Sources: the vendor docs linked in this article, checked Nov 17, 2026. “Not documented” means the page we opened does not mention it, not that the feature is absent. SQLite window functions arrived in 3.25.0 (2018-09-15).
Three practical notes. In SQL Server a ROWS or RANGE frame requires an ORDER BY, and ranking functions cannot take a frame at all. MySQL accepts NTH_VALUE and NTILE only with literal integer arguments, and only FROM FIRST works for NTH_VALUE. BigQuery allows a frame only on aggregate analytic functions. If you need the same query on two engines, check this table first, then read our notes on choosing a SQL editor that connects to both.
How do you calculate support resolution time in SQL?
Store one row per ticket event, then use FIRST_VALUE to get the opened time on the solved row, and LAG and LEAD to get the gaps between events. This answers “time to resolution per ticket” without a self-join.
The toy table is ticket_events. T-103 was solved with no reply, and T-104 is still open.
| ticket_id | event | event_at |
|---|---|---|
| T-101 | opened | 2026-03-02 09:00 |
| T-101 | replied | 2026-03-02 09:45 |
| T-101 | solved | 2026-03-02 14:30 |
| T-102 | opened | 2026-03-02 10:10 |
| T-102 | replied | 2026-03-03 08:05 |
| T-102 | solved | 2026-03-04 16:20 |
| T-103 | opened | 2026-03-03 13:00 |
| T-103 | solved | 2026-03-03 13:25 |
| T-104 | opened | 2026-03-04 09:00 |
| T-104 | replied | 2026-03-04 09:20 |
First, the gap before and after each event with LAG and LEAD. The minutes step uses SQLite’s julianday; swap in your engine’s date-difference function:
SELECT ticket_id, event, event_at,
LAG(event_at) OVER w AS prev_event_at,
ROUND((julianday(event_at) - julianday(LAG(event_at) OVER w)) * 1440) AS mins_since_prev,
ROUND((julianday(LEAD(event_at) OVER w) - julianday(event_at)) * 1440) AS mins_to_next
FROM ticket_events
WINDOW w AS (PARTITION BY ticket_id ORDER BY event_at);
| ticket_id | event | event_at | prev_event_at | mins_since_prev | mins_to_next |
|---|---|---|---|---|---|
| T-101 | opened | 2026-03-02 09:00 | NULL | NULL | 45 |
| T-101 | replied | 2026-03-02 09:45 | 2026-03-02 09:00 | 45 | 285 |
| T-101 | solved | 2026-03-02 14:30 | 2026-03-02 09:45 | 285 | NULL |
| T-102 | opened | 2026-03-02 10:10 | NULL | NULL | 1315 |
| T-102 | replied | 2026-03-03 08:05 | 2026-03-02 10:10 | 1315 | 1935 |
| T-102 | solved | 2026-03-04 16:20 | 2026-03-03 08:05 | 1935 | NULL |
| T-103 | opened | 2026-03-03 13:00 | NULL | NULL | 25 |
| T-103 | solved | 2026-03-03 13:25 | 2026-03-03 13:00 | 25 | NULL |
| T-104 | opened | 2026-03-04 09:00 | NULL | NULL | 20 |
| T-104 | replied | 2026-03-04 09:20 | 2026-03-04 09:00 | 20 | NULL |
Now time to resolution. FIRST_VALUE carries the opened time onto every row of the ticket, and the outer query keeps only the solved rows:
SELECT ticket_id, event_at AS solved_at, opened_at,
ROUND((julianday(event_at) - julianday(opened_at)) * 1440) AS minutes_to_resolve
FROM (
SELECT ticket_id, event, event_at,
FIRST_VALUE(event_at) OVER (PARTITION BY ticket_id ORDER BY event_at) AS opened_at
FROM ticket_events
) t
WHERE event = 'solved';
| ticket_id | solved_at | opened_at | minutes_to_resolve |
|---|---|---|---|
| T-101 | 2026-03-02 14:30 | 2026-03-02 09:00 | 330 |
| T-102 | 2026-03-04 16:20 | 2026-03-02 10:10 | 3250 |
| T-103 | 2026-03-03 13:25 | 2026-03-03 13:00 | 25 |
The average of the three solved tickets is (330 + 3,250 + 25) / 3 = 1,201.7 minutes. T-104 is absent because it has no solved event, which is right: an open ticket has no resolution time yet. For first-reply time, take LAG(event_at) on the replied row and subtract: that gives 45 minutes for T-101, 1,315 for T-102 and 20 for T-104.
Business hours need a calendar table
Wall-clock minutes count nights and weekends. To count only support hours, join each interval to a calendar or working-hours table and sum the overlap. Window functions give you the start and end of each interval. The calendar join does the rest.
Where should you practice next?
Run these queries on real data. Our list of SQL practice datasets names free databases for every engine in the table above. To see how a skill like this gets tested, read the SQL interview questions for data analysts. A window function can often replace a correlated subquery, and the two are worth comparing.
Questions people ask
What is a window function in SQL?
A window function calculates a value for every row using a set of related rows, called a window, and keeps each row in the result. A GROUP BY query returns one row per group instead. You write it with an OVER clause, as in SUM(amount) OVER (PARTITION BY region).
What is the difference between RANK and DENSE_RANK?
RANK leaves gaps after ties, so two rows tied for 2nd are followed by 4th. DENSE_RANK does not leave gaps, so the next rank is 3rd. PostgreSQL documents rank as the row_number of the first row in its peer group, and dense_rank as counting peer groups.
Why does LAST_VALUE return the current row?
With ORDER BY and no frame, most databases use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the last row of the frame is the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Snowflake documents a different default for LAST_VALUE: the whole window.
Can you use a window function in WHERE?
No. Window functions run after WHERE, GROUP BY and HAVING, so filter in an outer query or CTE. Snowflake, BigQuery and DuckDB also have QUALIFY, which filters on window results directly. PostgreSQL, MySQL 8, SQLite and SQL Server do not have it.
Which databases support IGNORE NULLS?
DuckDB and Snowflake document it, SQL Server 2022 and later support it, and BigQuery supports it on FIRST_VALUE, LAST_VALUE and NTH_VALUE only. PostgreSQL and MySQL 8 do not implement it. Their docs say the behavior is always RESPECT NULLS.
How do you calculate time to resolution per ticket in SQL?
Put each ticket event in one row, then use FIRST_VALUE or LAG over PARTITION BY ticket_id ORDER BY event_at. Subtract the opened time from the solved time on the solved row. The worked example below does this on a four-ticket table.
Sources
- PostgreSQL docs: Window functions (built-in) ·
- PostgreSQL docs: Window function calls and frame clause ·
- PostgreSQL 11 release notes (GROUPS mode, frame exclusion) ·
- MySQL 8.0 reference: Window function descriptions ·
- MySQL 8.0 reference: Window function frame specification ·
- Microsoft Learn: SELECT - OVER clause (Transact-SQL) ·
- Microsoft Learn: LAG (Transact-SQL) ·
- Microsoft Learn: SELECT - WINDOW clause (Transact-SQL) ·
- Snowflake docs: Window function syntax and usage ·
- Snowflake docs: QUALIFY ·
- Snowflake docs: LAG ·
- BigQuery docs: Window function calls ·
- BigQuery docs: Query syntax (QUALIFY clause) ·
- BigQuery docs: Navigation functions ·
- SQLite docs: Window functions ·
- DuckDB docs: Window functions ·
- DuckDB docs: QUALIFY clause ·