(opens in a new tab)

SQL Window Functions Cheat Sheet with Examples

August 19, 2026 by Chat2DB Team

Window functions let you compute across a set of rows while keeping every row in the output. That single property — aggregate without collapsing — is what makes running totals, rankings, period-over-period comparisons and deduplication straightforward instead of requiring self-joins.

This is a cheat sheet you can work from, with every example runnable against the schema below.

Sample data

CREATE TABLE sales (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region     text        NOT NULL,
  salesperson text       NOT NULL,
  sale_date  date        NOT NULL,
  amount     numeric(10,2) NOT NULL
);
 
INSERT INTO sales (region, salesperson, sale_date, amount) VALUES
('North', 'Alice',  '2026-01-05', 1200.00),
('North', 'Alice',  '2026-02-11',  850.00),
('North', 'Bob',    '2026-01-18', 2100.00),
('North', 'Bob',    '2026-03-02',  975.00),
('South', 'Carol',  '2026-01-09', 1750.00),
('South', 'Carol',  '2026-02-20', 1750.00),
('South', 'Dan',    '2026-01-25',  640.00),
('South', 'Dan',    '2026-03-14', 3200.00),
('East',  'Erin',   '2026-02-01',  480.00),
('East',  'Erin',   '2026-03-22', 1990.00);

The anatomy of a window function

function_name(args) OVER (
  PARTITION BY  col      -- optional: split rows into groups
  ORDER BY      col      -- optional: order within each group
  frame_clause           -- optional: which rows within the group
)

Three independent pieces:

• PARTITION BY divides rows into groups. The function restarts for each group. Omit it and the whole result set is one partition.
• ORDER BY sets the order within each partition. Required for ranking and offset functions to mean anything.
• Frame clause narrows the window further, to a sliding range of rows relative to the current one. This is what makes running totals and moving averages possible.

An empty OVER () means "every row in the result set, unordered" — useful for adding a grand total to each row:

SELECT
  salesperson,
  amount,
  sum(amount) OVER ()                       AS grand_total,
  round(100 * amount / sum(amount) OVER (), 2) AS pct_of_total
FROM sales;

Ranking functions

SELECT
  region,
  salesperson,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank,
  NTILE(2)     OVER (PARTITION BY region ORDER BY amount DESC) AS half
FROM sales
ORDER BY region, amount DESC;

How they differ on ties — Carol has two sales of exactly 1750.00:

ROW_NUMBER() being non-deterministic on ties matters. If you deduplicate with it and two rows tie on the ordering column, which one survives is arbitrary and can change between runs. Add a tiebreaker:

ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id)

Two more ranking functions return positions as fractions:

SELECT
  salesperson,
  amount,
  round(PERCENT_RANK() OVER (ORDER BY amount)::numeric, 3) AS pct_rank,
  round(CUME_DIST()    OVER (ORDER BY amount)::numeric, 3) AS cume_dist
FROM sales;

PERCENT_RANK() is (rank - 1) / (total_rows - 1), so it always starts at 0. CUME_DIST() is the fraction of rows at or below the current one, so it always ends at 1.

Offset functions: LAG and LEAD

These read a value from another row without a self-join — the standard way to compute deltas.

SELECT
  salesperson,
  sale_date,
  amount,
  LAG(amount)  OVER (PARTITION BY salesperson ORDER BY sale_date) AS prev_amount,
  LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS next_amount,
  amount - LAG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS delta
FROM sales
ORDER BY salesperson, sale_date;

The first row of each partition has no previous row, so LAG returns NULL and the delta is NULL. Supply a default as the third argument:

SELECT
  salesperson,
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sale_date) AS delta
FROM sales
ORDER BY salesperson, sale_date;

The second argument is the offset — LAG(amount, 2) looks two rows back. Percentage change is the common follow-up:

SELECT
  salesperson,
  sale_date,
  amount,
  round(
    100.0 * (amount - LAG(amount) OVER w) / NULLIF(LAG(amount) OVER w, 0),
    1
  ) AS pct_change
FROM sales
WINDOW w AS (PARTITION BY salesperson ORDER BY sale_date)
ORDER BY salesperson, sale_date;

Two things to note. NULLIF(..., 0) prevents division by zero. And the WINDOW clause names a window definition so you write it once — worth using the moment the same OVER (...) appears twice.

Frame clauses

Without a frame, ORDER BY inside OVER() applies the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default is why the following two queries give different answers:

-- No ORDER BY: total per partition, same on every row
SELECT salesperson, amount,
       sum(amount) OVER (PARTITION BY salesperson) AS total
FROM sales;
 
-- With ORDER BY: running total, grows row by row
SELECT salesperson, sale_date, amount,
       sum(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS running_total
FROM sales;

Frame syntax:

{ ROWS | RANGE | GROUPS } BETWEEN <start> AND <end>

Where each bound is UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.

ROWS counts physical rows. Predictable and what you usually want:

-- Three-row moving average
SELECT
  salesperson,
  sale_date,
  amount,
  round(avg(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3
FROM sales
ORDER BY salesperson, sale_date;

RANGE works on values, grouping peers together. With RANGE, all rows with the same ORDER BY value are in the frame together — so Carol's two 1750.00 sales both appear in each other's frame when ordering by amount. RANGE also accepts intervals for date arithmetic:

-- Rolling 30-day total, by actual calendar days rather than row count
SELECT
  salesperson,
  sale_date,
  amount,
  sum(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
  ) AS rolling_30d
FROM sales
ORDER BY salesperson, sale_date;

This is the frame most people actually want for time series, and the one they usually get wrong by reaching for ROWS first. ROWS BETWEEN 29 PRECEDING means "the last 30 rows", which is only the last 30 days if you have exactly one row per day.

GROUPS (Postgres 11+) counts distinct peer groups rather than rows.

Value functions

SELECT
  region,
  salesperson,
  amount,
  FIRST_VALUE(salesperson) OVER w AS top_seller,
  LAST_VALUE(salesperson)  OVER w AS bottom_seller,
  NTH_VALUE(salesperson, 2) OVER w AS second_seller
FROM sales
WINDOW w AS (
  PARTITION BY region
  ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY region, amount DESC;

The explicit frame is essential. With the default frame, LAST_VALUE looks only as far as the current row, so it returns the current row's value on every row — the single most reported window-function surprise. UNBOUNDED FOLLOWING fixes it.

Common patterns

Deduplication

Keep one row per group:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY salesperson
           ORDER BY sale_date DESC, id DESC
         ) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

To actually delete duplicates:

DELETE FROM sales
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (
      PARTITION BY salesperson, sale_date, amount ORDER BY id
    ) AS rn
    FROM sales
  ) t
  WHERE rn > 1
);

Top N per group

SELECT region, salesperson, amount
FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
  FROM sales
) t
WHERE rnk <= 2
ORDER BY region, amount DESC;

DENSE_RANK rather than ROW_NUMBER here, so genuine ties both make the cut.

Note that you cannot filter on a window function in WHERE — window functions are evaluated after WHERE. Wrap in a subquery or CTE, or use QUALIFY in the databases that support it (Snowflake, BigQuery, DuckDB; not Postgres or MySQL).

Gaps and islands

Find consecutive runs by subtracting a row number from a sequence:

SELECT
  salesperson,
  min(sale_date) AS run_start,
  max(sale_date) AS run_end,
  count(*)       AS days_in_run
FROM (
  SELECT
    salesperson,
    sale_date,
    sale_date - (ROW_NUMBER() OVER (
      PARTITION BY salesperson ORDER BY sale_date
    ))::int AS grp
  FROM sales
) t
GROUP BY salesperson, grp
ORDER BY salesperson, run_start;

Rows on consecutive dates produce a constant grp, so grouping on it collapses each run.

Cumulative percentage

SELECT
  salesperson,
  sale_date,
  amount,
  sum(amount) OVER (ORDER BY sale_date, id) AS running_total,
  round(100.0 * sum(amount) OVER (ORDER BY sale_date, id)
        / sum(amount) OVER (), 2) AS cumulative_pct
FROM sales
ORDER BY sale_date, id;

Database support

The MySQL boundary is the one that catches people out — a query that works fine on 8.0 fails outright on 5.7 with a syntax error.

Performance notes

Window functions need their input sorted by PARTITION BY then ORDER BY. A matching index avoids the sort:

CREATE INDEX sales_person_date_idx ON sales (salesperson, sale_date);

Check whether it helped:

EXPLAIN (ANALYZE, BUFFERS)
SELECT salesperson, sale_date,
       sum(amount) OVER (PARTITION BY salesperson ORDER BY sale_date)
FROM sales;

A Sort node feeding a WindowAgg means the index is not being used for ordering. On large result sets watch for Sort Method: external merge Disk: ... — that is a spill, and raising work_mem for the session usually fixes it:

SET work_mem = '128MB';

Multiple window functions sharing an identical OVER clause are computed in a single pass, which is another reason to use the WINDOW clause — it makes the sharing obvious to you as well as to the planner.

If you would rather not hand-write the OVER clause each time, the SQL window function generator (opens in a new tab) builds these clauses for Postgres, MySQL, SQL Server, Oracle and more, including the frame options. Chat2DB (opens in a new tab) will also generate and explain window queries against your actual schema.

Summary

Window functions aggregate without collapsing rows. PARTITION BY groups, ORDER BY sequences within a group, and the frame clause selects which rows around the current one participate.

Remember the four things that trip people up: ROW_NUMBER is arbitrary on ties unless you add a tiebreaker; LAST_VALUE needs an explicit UNBOUNDED FOLLOWING frame; ROWS counts rows while RANGE counts values and is what you want for time-based windows; and you cannot filter on a window function in WHERE — wrap it in a subquery first.