SQL Window Functions: Complete Guide with Examples

SQL window functions perform calculations across a set of rows related to the current row — without collapsing the result into a single value like GROUP BY does. They unlock patterns that are nearly impossible with standard aggregation: running totals, moving averages, rank within a group, previous-row comparisons, and more.

This guide covers every window function with working examples you can run in PostgreSQL, MySQL 8+, or SQLite 3.25+.

Quick Reference

Anatomy of a Window Function

function_name(expression)
OVER (
  PARTITION BY column1, column2   -- optional: divide rows into groups
  ORDER BY column3 DESC           -- optional: order within each group
  ROWS BETWEEN ... AND ...        -- optional: limit which rows are included
)

Setup: Sample Tables

CREATE TABLE sales (
  id          INT,
  rep         VARCHAR(50),
  region      VARCHAR(50),
  sale_date   DATE,
  amount      DECIMAL(10,2)
);

INSERT INTO sales VALUES
  (1,  'Alice', 'North', '2025-01-05', 1200.00),
  (2,  'Bob',   'North', '2025-01-08', 850.00),
  (3,  'Alice', 'North', '2025-01-15', 2100.00),
  (4,  'Carol', 'South', '2025-01-10', 950.00),
  (5,  'Dave',  'South', '2025-01-12', 1750.00),
  (6,  'Carol', 'South', '2025-02-01', 3200.00),
  (7,  'Bob',   'North', '2025-02-05', 1100.00),
  (8,  'Dave',  'South', '2025-02-08', 600.00),
  (9,  'Alice', 'North', '2025-02-20', 1800.00),
  (10, 'Carol', 'South', '2025-03-01', 2500.00);

1. Ranking Functions

ROW_NUMBER()

Assigns a unique sequential integer to each row within its partition. Ties get different numbers.

SELECT
  rep,
  region,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM sales;

Common use case: Get the top N rows per group.

-- Top 2 sales per region
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) ranked
WHERE rn <= 2;

RANK() and DENSE_RANK()

Both assign the same rank to tied rows. They differ in how they handle the next rank after a tie:

SELECT
  rep,
  amount,
  RANK()       OVER (ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM sales;

Example output when Alice has 2100 and Carol has 2100 (tied):

RANK() skips rank 2; DENSE_RANK() does not.

NTILE(n)

Divides rows into n equally-sized buckets and assigns a bucket number.

SELECT
  rep,
  amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales;

Use NTILE(100) for percentiles, NTILE(10) for deciles.

2. Offset Functions

LAG() and LEAD()

Access a value from a previous (LAG) or following (LEAD) row within the partition.

LAG(expression, offset, default) OVER (...)
LEAD(expression, offset, default) OVER (...)

Month-over-month growth:

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

FIRST_VALUE() and LAST_VALUE()

Return the first or last value in the window frame.

SELECT
  rep,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY rep ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_sale,
  LAST_VALUE(amount) OVER (
    PARTITION BY rep ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_sale
FROM sales;

Important: LAST_VALUE uses a default frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so you almost always need to extend it to UNBOUNDED FOLLOWING to get the actual last value.

NTH_VALUE()

Returns the value of a specific row within the window frame.

SELECT
  rep,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY region ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest
FROM sales;

3. Aggregate Window Functions

Any standard aggregate (SUM, COUNT, AVG, MIN, MAX) can be used with OVER() to compute running or windowed values without collapsing rows.

Running Total

SELECT
  rep,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Running Total per Partition

SELECT
  rep,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY rep
    ORDER BY sale_date
  ) AS rep_running_total
FROM sales;

Each rep's running total resets independently.

Moving Average (7-day)

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM sales;

Percentage of Total

SELECT
  rep,
  region,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (PARTITION BY region),
    2
  ) AS pct_of_region
FROM sales;

4. Window Frame Clause

The frame clause limits which rows are included in aggregate calculations.

ROWS BETWEEN start AND end
RANGE BETWEEN start AND end

Frame boundary options:

ROWS vs RANGE:

Default frame (when ORDER BY is present): RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

5. Distribution Functions

PERCENT_RANK()

Returns the relative rank of a row as a value between 0 and 1.

(rank - 1) / (total rows - 1)

SELECT
  rep,
  amount,
  ROUND(PERCENT_RANK() OVER (ORDER BY amount), 4) AS pct_rank
FROM sales;

The lowest value gets 0.0; the highest gets 1.0.

CUME_DIST()

Returns the cumulative distribution — the fraction of rows with values ≤ the current row.

SELECT
  rep,
  amount,
  ROUND(CUME_DIST() OVER (ORDER BY amount), 4) AS cume_dist
FROM sales;

CUME_DIST always gives a value > 0 (unlike PERCENT_RANK which can be 0).

6. Common Patterns

Top N per Group

-- Top 3 sales reps per region by total sales
SELECT region, rep, total FROM (
  SELECT
    region,
    rep,
    SUM(amount) AS total,
    RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk
  FROM sales
  GROUP BY region, rep
) ranked
WHERE rnk <= 3;

Previous Row Comparison (YoY, MoM)

SELECT
  rep,
  EXTRACT(MONTH FROM sale_date) AS month,
  SUM(amount) AS monthly_total,
  LAG(SUM(amount)) OVER (
    PARTITION BY rep
    ORDER BY EXTRACT(MONTH FROM sale_date)
  ) AS prev_month_total,
  ROUND(
    100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (
      PARTITION BY rep ORDER BY EXTRACT(MONTH FROM sale_date)
    )) / NULLIF(LAG(SUM(amount)) OVER (
      PARTITION BY rep ORDER BY EXTRACT(MONTH FROM sale_date)
    ), 0),
    2
  ) AS mom_pct_change
FROM sales
GROUP BY rep, EXTRACT(MONTH FROM sale_date);

Deduplication (Keep Latest Row per Group)

-- Keep only the most recent sale per rep
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY rep ORDER BY sale_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1;

Consecutive Streaks (Islands and Gaps)

-- Find groups of consecutive days with sales
SELECT
  rep,
  sale_date,
  ROW_NUMBER() OVER (PARTITION BY rep ORDER BY sale_date) AS rn,
  sale_date - ROW_NUMBER() OVER (PARTITION BY rep ORDER BY sale_date) * INTERVAL '1 day' AS grp
FROM sales;

Then GROUP BY rep, grp to count consecutive days in each streak.

Running Distinct Count

-- Approximate: count distinct reps seen so far (ordered by date)
SELECT
  sale_date,
  rep,
  COUNT(*) OVER (
    PARTITION BY rep
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rep_sale_number
FROM sales;

7. Window Functions vs GROUP BY

Example — GROUP BY:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;
-- Returns 2 rows (one per region)

Window equivalent (keeps all rows):

SELECT rep, region, amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
-- Returns 10 rows, each with the region total attached

8. Database Compatibility

MySQL added window function support in version 8.0. If you're on MySQL 5.7 or earlier, you'll need subqueries or variables to replicate this behavior.

Common Mistakes

Named Windows

PostgreSQL and MySQL support defining a named window to reuse OVER clauses:

SELECT
  rep,
  amount,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rnk,
  SUM(amount)  OVER w AS running_total
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date);

FAQ

Can I filter on a window function result in WHERE? No. Window functions run after WHERE and GROUP BY. Wrap the query in a subquery or CTE:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) t
WHERE rn = 1;

Can I use window functions with GROUP BY in the same query? Yes — but the window function is computed after grouping. The OVER() clause sees the grouped result, not individual rows:

SELECT rep, SUM(amount) AS total,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM sales
GROUP BY rep;

What's the difference between ROWS and RANGE? ROWS counts physical row offsets. RANGE uses logical value offsets. For most use cases (running totals, moving averages by row count), ROWS is what you want. Use RANGE only when you want to include all rows with the same value as the current row.

Do window functions work with CTEs? Yes, and CTEs are the best way to layer window functions:

WITH ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drn
  FROM sales
)
SELECT * FROM ranked WHERE drn <= 3;

Are window functions slower than GROUP BY? Window functions add a sort step, which has O(n log n) cost. They are generally comparable to GROUP BY — and far faster than correlated subqueries for the same result. An index on (PARTITION BY column, ORDER BY column) can eliminate the sort.

Does SQLite support window functions? Yes, since version 3.25.0 (released September 2018). All modern SQLite distributions include window function support.