Guide

SQL window functions cheat sheet

Syntax, ranking, offsets, frames and QUALIFY on one small table, with the input and output shown, plus what changes between seven databases.

By Updated 11 min read

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
  1. What is the syntax of a SQL window function?
  2. Which ranking function should you use?
  3. How do LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE work?
  4. How do you calculate a running total and a moving average?
  5. What is the difference between ROWS, RANGE and GROUPS?
  6. How do you use aggregate functions as window functions?
  7. How do you filter on a window function with QUALIFY?
  8. How do the databases differ on window functions?
  9. How do you calculate support resolution time in SQL?
  10. 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 BY collapses rows; OVER (...) does not.
  • The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in PostgreSQL, MySQL 8, SQL Server and BigQuery, which makes tied rows share a running total.
  • LAST_VALUE returns the current row unless you widen the frame. Snowflake documents a whole-window default for it instead.
  • GROUPS frames work in PostgreSQL 11+, SQLite 3.28+ and DuckDB. MySQL 8, SQL Server, Snowflake and BigQuery document only ROWS and RANGE.
  • QUALIFY exists in Snowflake, BigQuery and DuckDB. SQL Server has it only in Fabric Data Warehouse.
  • IGNORE NULLS works 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):

repregionsale_dateamount
AnaEast2026-03-01100
BenEast2026-03-02250
CyEast2026-03-03250
DeeEast2026-03-04400
EliWest2026-03-01300
FayWest2026-03-02100
GusWest2026-03-03300
HalWest2026-03-04200

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;
regionrepamountrnrnkdrnkhalf
EastDee4001111
EastBen2502221
EastCy2503222
EastAna1004432
WestEli3001111
WestGus3002111
WestHal2003322
WestFay1004432

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);
regionsale_dateamountprev_amountnext_amountfirst_amtlast_defaultlast_fullsecond_amt
East2026-03-01100NULL250100100400250
East2026-03-02250100250100250400250
East2026-03-03250250400100250400250
East2026-03-04400250NULL100400400250
West2026-03-01300NULL100300300200100
West2026-03-02100300300300100200100
West2026-03-03300100200300300200100
West2026-03-04200300NULL300200200100

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;
regionsale_dateamountrunning_totalavg_3
East2026-03-01100100100
East2026-03-02250350175
East2026-03-03250600200
East2026-03-044001000300
West2026-03-01300300300
West2026-03-02100400200
West2026-03-03300700233.3
West2026-03-04200900200

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';
repamountdefault_framerows_frame
Ana100100100
Ben250600350
Cy250600600
Dee40010001000

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;
repamountsum_two_groups
Ana100200
Fay100200
Hal200400
Ben250700
Cy250700
Eli3001100
Gus3001100
Dee4001000

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;
regionrepamountregion_totalpct_of_region
EastAna100100010
EastBen250100025
EastCy250100025
EastDee400100040
WestEli30090033.3
WestFay10090011.1
WestGus30090033.3
WestHal20090022.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;
regionrepamount
EastDee400
EastBen250
WestEli300
WestGus300

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.

DatabaseFrame unitsGROUPSIGNORE NULLSNamed WINDOWQUALIFY
PostgreSQLROWS, RANGE, GROUPSYes (11+)No, always RESPECT NULLSYesNo
MySQL 8.0ROWS, RANGENoParsed, but errorsYesNo
SQL ServerROWS, RANGE (no RANGE offsets)NoYes (2022+)Yes (2022+, compat level 160)Fabric Data Warehouse only
SnowflakeROWS, RANGENot documentedYes (e.g. LAG)Not confirmed in the docs we openedYes
BigQueryROWS, RANGENot documentedFIRST_VALUE, LAST_VALUE, NTH_VALUE onlyYesYes
SQLiteROWS, RANGE, GROUPSYes (3.28+)Not documentedYesNo
DuckDBROWS, RANGE, GROUPSYesYesYesYes

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_ideventevent_at
T-101opened2026-03-02 09:00
T-101replied2026-03-02 09:45
T-101solved2026-03-02 14:30
T-102opened2026-03-02 10:10
T-102replied2026-03-03 08:05
T-102solved2026-03-04 16:20
T-103opened2026-03-03 13:00
T-103solved2026-03-03 13:25
T-104opened2026-03-04 09:00
T-104replied2026-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_ideventevent_atprev_event_atmins_since_prevmins_to_next
T-101opened2026-03-02 09:00NULLNULL45
T-101replied2026-03-02 09:452026-03-02 09:0045285
T-101solved2026-03-02 14:302026-03-02 09:45285NULL
T-102opened2026-03-02 10:10NULLNULL1315
T-102replied2026-03-03 08:052026-03-02 10:1013151935
T-102solved2026-03-04 16:202026-03-03 08:051935NULL
T-103opened2026-03-03 13:00NULLNULL25
T-103solved2026-03-03 13:252026-03-03 13:0025NULL
T-104opened2026-03-04 09:00NULLNULL20
T-104replied2026-03-04 09:202026-03-04 09:0020NULL

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_idsolved_atopened_atminutes_to_resolve
T-1012026-03-02 14:302026-03-02 09:00330
T-1022026-03-04 16:202026-03-02 10:103250
T-1032026-03-03 13:252026-03-03 13:0025

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

  1. PostgreSQL docs: Window functions (built-in) ·
  2. PostgreSQL docs: Window function calls and frame clause ·
  3. PostgreSQL 11 release notes (GROUPS mode, frame exclusion) ·
  4. MySQL 8.0 reference: Window function descriptions ·
  5. MySQL 8.0 reference: Window function frame specification ·
  6. Microsoft Learn: SELECT - OVER clause (Transact-SQL) ·
  7. Microsoft Learn: LAG (Transact-SQL) ·
  8. Microsoft Learn: SELECT - WINDOW clause (Transact-SQL) ·
  9. Snowflake docs: Window function syntax and usage ·
  10. Snowflake docs: QUALIFY ·
  11. Snowflake docs: LAG ·
  12. BigQuery docs: Window function calls ·
  13. BigQuery docs: Query syntax (QUALIFY clause) ·
  14. BigQuery docs: Navigation functions ·
  15. SQLite docs: Window functions ·
  16. DuckDB docs: Window functions ·
  17. DuckDB docs: QUALIFY clause ·

Start hereExplainer

Open