# SQL Window Functions Explained: From ROW_NUMBER to Running Totals

Sooner or later every developer writes a query like this one:

-   "Show every order, _and_ the customer's total spend next to it."
    
-   "Give me the top 3 products in each category."
    
-   "Add a running total column."
    
-   "Compare each month's revenue with the previous month."
    

The first instinct is usually a self-join or a correlated subquery. It works, it's slow, and six months later nobody wants to read it.

**Window functions** are the clean answer to all four. Once they click, you'll reach for them weekly.

> All examples use PostgreSQL syntax, but window functions are standard SQL. They work in PostgreSQL 8.4+, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+, SQL Server 2012+, Oracle, BigQuery, and Snowflake. Check your version if something errors out.

## The one idea: keep the rows, add a calculation

`GROUP BY` squashes many rows into one. A window function does the same kind of math, but **keeps every row** and attaches the result to it.

Here's the dataset we'll use for the whole post. Paste it into `psql` or any SQL playground:

```sql
CREATE TABLE sales (
  id        INT PRIMARY KEY,
  region    TEXT,
  rep       TEXT,
  sale_date DATE,
  amount    INT
);

INSERT INTO sales VALUES
(1, 'North', 'Asha',  '2026-01-05', 500),
(2, 'North', 'Ravi',  '2026-01-12', 300),
(3, 'North', 'Asha',  '2026-02-03', 700),
(4, 'North', 'Meera', '2026-02-20', 500),
(5, 'South', 'Kabir', '2026-01-08', 400),
(6, 'South', 'Neha',  '2026-01-25', 600),
(7, 'South', 'Kabir', '2026-02-14', 500),
(8, 'South', 'Neha',  '2026-03-02', 200);
```

With `GROUP BY` you get one row per region:

```sql
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;
```

| region | region_total |
| --- | --- |
| North | 2000 |
| South | 1700 |

With a window function you get all 8 rows, each carrying its region's total:

```sql
SELECT id, region, rep, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY id;
```

| id | region | rep | amount | region_total |
| --- | --- | --- | --- | --- |
| 1 | North | Asha | 500 | 2000 |
| 2 | North | Ravi | 300 | 2000 |
| 3 | North | Asha | 700 | 2000 |
| 4 | North | Meera | 500 | 2000 |
| 5 | South | Kabir | 400 | 1700 |
| 6 | South | Neha | 600 | 1700 |
| 7 | South | Kabir | 500 | 1700 |
| 8 | South | Neha | 200 | 1700 |

That `OVER (...)` clause is what turns an ordinary function into a window function.

## Anatomy of an OVER clause

```sql
function_name(arguments) OVER (
  PARTITION BY column_a, column_b   -- optional: split rows into groups
  ORDER BY column_c                 -- optional: order rows inside each group
  ROWS BETWEEN ... AND ...          -- optional: which rows the function can see
)
```

Each part has one job:

-   `PARTITION BY` splits the rows into independent groups (the "windows"). Leave it out and the whole result set is one group.
    
-   `ORDER BY` sorts the rows _inside_ each group. Ranking and "previous row" logic need it.
    
-   **The frame** (`ROWS BETWEEN ...`) narrows down which rows, relative to the current one, the function actually looks at.
    

For every row, the database does this:

![Mermaid Diagram](https://mermaid.ink/img/pako:eNo90FFr2zAQB_CvcrtnOWPQpzAKdmK3oU1TlGajxHmQ7XMsJktGOifrSr57scLyKHG_-_-5T6xdQzjH1rhz3SnP8CxLCwCQ7kuU7hzAUMugWib_s_Lf7wu5WQv4_ZjLXMCD3OxeIXsv8QBJcg_ZvsTXVL6t3labF8jeowiD0QzasoOjd-MQSjxcM7KIFvsSN3KZyxtwfpoPuiEgVXdXdlOLqJb7EguveorET01POujKELAD7gjq0XuyDN6db3YZbT7Z0dasnQU_2hB3bHdrATJ9eRLwnD7AbDa7sTyyYjoJhdEwKGZVd9T8z_LuHHdYB7GK8gSeeneipsQDCuzJ90o3OP9E7qifbt5Qq0bDKK4_v5TXqjIUppnWWS5Ur80HzjFRw2AoCR-BqReQGW3_rFW9je_CWRZQ4paOjmC3KlGAdJVjJ-CRzIlY10pA6rUyAoKyIQnkdYsihmz1v6nLj7vhL14uAqvjwhnncY7fzp1mwssXTdmv6Q?type=png)

## Aggregates as window functions

Any aggregate (`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`) can become a window function just by adding `OVER`. That makes "share of the group" and "share of the total" queries trivial:

```sql
SELECT id, region, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1)                    AS pct_of_total
FROM sales
ORDER BY id;
```

| id | region | amount | pct_of_region | pct_of_total |
| --- | --- | --- | --- | --- |
| 1 | North | 500 | 25.0 | 13.5 |
| 2 | North | 300 | 15.0 | 8.1 |
| 3 | North | 700 | 35.0 | 18.9 |
| 4 | North | 500 | 25.0 | 13.5 |
| 5 | South | 400 | 23.5 | 10.8 |
| 6 | South | 600 | 35.3 | 16.2 |
| 7 | South | 500 | 29.4 | 13.5 |
| 8 | South | 200 | 11.8 | 5.4 |

The empty `OVER ()` is the "no `PARTITION BY`" case from above: the whole result is one window, so `pct_of_total` divides by the grand total (3700).

## Ranking: ROW\_NUMBER, RANK, DENSE\_RANK

These three look alike but handle **ties** differently. North has two sales of 500, which makes the difference easy to see:

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

| id | region | rep | amount | row_num | rnk | dense_rnk |
| --- | --- | --- | --- | --- | --- | --- |
| 3 | North | Asha | 700 | 1 | 1 | 1 |
| 1 | North | Asha | 500 | 2 | 2 | 2 |
| 4 | North | Meera | 500 | 3 | 2 | 2 |
| 2 | North | Ravi | 300 | 4 | 4 | 3 |
| 6 | South | Neha | 600 | 1 | 1 | 1 |
| 7 | South | Kabir | 500 | 2 | 2 | 2 |
| 5 | South | Kabir | 400 | 3 | 3 | 3 |
| 8 | South | Neha | 200 | 4 | 4 | 4 |

-   `ROW_NUMBER()` numbers every row uniquely, even on ties. Which tied row comes first is arbitrary unless you add a tiebreaker, which is why `id` is in the `ORDER BY` above. Without one, the numbering can change from run to run.
    
-   `RANK()` gives ties the same rank, then **skips** ahead: two rows at rank 2, so the next is rank 4.
    
-   `DENSE_RANK()` gives ties the same rank and **never skips**: after two rank-2 rows comes rank 3.
    

Need buckets instead of ranks? `NTILE(4)` deals the rows into four roughly equal buckets, which is handy for quartiles.

### Top-N per group

This is the most common use case. You can't filter on a window function in the same query's `WHERE` (see Pitfalls), so compute the rank in a CTE and filter outside:

```sql
WITH ranked AS (
  SELECT id, region, rep, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
  FROM sales
)
SELECT id, region, rep, amount
FROM ranked
WHERE rn <= 2;
```

| id | region | rep | amount |
| --- | --- | --- | --- |
| 3 | North | Asha | 700 |
| 1 | North | Asha | 500 |
| 6 | South | Neha | 600 |
| 7 | South | Kabir | 500 |

Use `ROW_NUMBER()` when you want exactly N rows per group. Use `RANK()` or `DENSE_RANK()` when ties should all be included.

## Looking at neighbouring rows: LAG and LEAD

`LAG(col, n)` looks `n` rows **back** within the partition and `LEAD(col, n)` looks `n` rows **ahead**. `n` defaults to 1. If there's no such row you get `NULL`, or a default you pass as the third argument.

Month-over-month change is the textbook example. Aggregate first, then look backwards:

```sql
WITH monthly AS (
  SELECT region,
         DATE_TRUNC('month', sale_date)::date AS month,
         SUM(amount) AS revenue
  FROM sales
  GROUP BY region, DATE_TRUNC('month', sale_date)
)
SELECT region, month, revenue,
       LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS change
FROM monthly
ORDER BY region, month;
```

| region | month | revenue | prev_revenue | change |
| --- | --- | --- | --- | --- |
| North | 2026-01-01 | 800 | NULL | NULL |
| North | 2026-02-01 | 1200 | 800 | 400 |
| South | 2026-01-01 | 1000 | NULL | NULL |
| South | 2026-02-01 | 500 | 1000 | -500 |
| South | 2026-03-01 | 200 | 500 | -300 |

Notice the `PARTITION BY region`. Without it, the rows of both regions are sorted into one list by month, so each row gets compared with whichever region's row happens to sit just before it. South's January would show North's 800 as its "previous" month. There's no error, just wrong numbers.

> **Tip:** Because window functions run _after_ `GROUP BY` (more in Pitfalls), they can take aggregates as arguments. `LAG(SUM(amount)) OVER (...)` works directly in a grouped query, no CTE required. The CTE version is just easier to read.

On SQLite, replace both occurrences of `DATE_TRUNC('month', sale_date)` (and the `::date` cast) with `strftime('%Y-%m-01', sale_date)`.

## Running totals and moving averages

Add an `ORDER BY` inside `OVER` to an aggregate and it becomes **cumulative**. Add an explicit **frame** and you control the window size:

```sql
SELECT id, region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total,
       ROUND(AVG(amount) OVER (
         PARTITION BY region ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS moving_avg_3
FROM sales
ORDER BY region, sale_date;
```

| id | region | sale_date | amount | running_total | moving_avg_3 |
| --- | --- | --- | --- | --- | --- |
| 1 | North | 2026-01-05 | 500 | 500 | 500.0 |
| 2 | North | 2026-01-12 | 300 | 800 | 400.0 |
| 3 | North | 2026-02-03 | 700 | 1500 | 500.0 |
| 4 | North | 2026-02-20 | 500 | 2000 | 500.0 |
| 5 | South | 2026-01-08 | 400 | 400 | 400.0 |
| 6 | South | 2026-01-25 | 600 | 1000 | 500.0 |
| 7 | South | 2026-02-14 | 500 | 1500 | 500.0 |
| 8 | South | 2026-03-02 | 200 | 1700 | 433.3 |

The 3-row moving average for the last South row is `(600 + 500 + 200) / 3 = 433.3`. At the start of each partition there aren't 3 rows yet, so the average uses however many exist.

### Understanding frames

Frame bounds are built from these pieces:

| Bound | Meaning |
| --- | --- |
| UNBOUNDED PRECEDING | from the first row of the partition |
| n PRECEDING | n rows before the current row |
| CURRENT ROW | the current row |
| n FOLLOWING | n rows after the current row |
| UNBOUNDED FOLLOWING | to the last row of the partition |

Two defaults are worth memorising:

1.  **No** `ORDER BY` in the `OVER` clause: the frame is the _whole partition_.
    
2.  **With** `ORDER BY` **and no explicit frame**: the frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. That's what makes a running total work, but it also causes the most common window-function bug.
    

### The LAST\_VALUE gotcha

Because of that default frame, `LAST_VALUE` doesn't return the last row of the partition. It returns the last row _up to the current one_, which is usually the current row itself:

```sql
SELECT id, rep, sale_date,
       FIRST_VALUE(rep) OVER (PARTITION BY region ORDER BY sale_date) AS first_rep,
       LAST_VALUE(rep)  OVER (PARTITION BY region ORDER BY sale_date) AS last_rep_wrong,
       LAST_VALUE(rep)  OVER (
         PARTITION BY region ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_rep
FROM sales
WHERE region = 'North'
ORDER BY sale_date;
```

| id | rep | sale_date | first_rep | last_rep_wrong | last_rep |
| --- | --- | --- | --- | --- | --- |
| 1 | Asha | 2026-01-05 | Asha | Asha | Meera |
| 2 | Ravi | 2026-01-12 | Asha | Ravi | Meera |
| 3 | Asha | 2026-02-03 | Asha | Asha | Meera |
| 4 | Meera | 2026-02-20 | Asha | Meera | Meera |

`FIRST_VALUE` is fine with the default frame. For `LAST_VALUE` (and `NTH_VALUE`), spell the frame out.

### ROWS vs RANGE

-   `ROWS` counts physical rows. "2 PRECEDING" means exactly two rows back.
    
-   `RANGE` works on _values_ of the `ORDER BY` column, and includes every **peer** row that has the same value.
    

If two sales share a date, `SUM(amount) OVER (ORDER BY sale_date)` gives both rows the same running total, because they are peers. When that surprises you, either add a unique tiebreaker to the `ORDER BY` (like `id`) or switch to `ROWS`.

## Two real-world recipes

### 1\. Deduplicate: keep the latest row per key

Duplicated signups, repeated webhook deliveries, multiple versions of a record. This is the top-N pattern from earlier with N = 1: number the rows per key, newest first, and keep number 1.

```sql
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM signups
)
SELECT * FROM ranked WHERE rn = 1;
```

To delete the older duplicates instead, keep the same CTE and change the last statement:

```sql
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, id DESC) AS rn
  FROM signups
)
DELETE FROM signups
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
```

Run the `SELECT` version first and check the row count before you delete anything.

### 2\. Gaps and islands: find consecutive streaks

"How many days in a row did each user log in?" Subtract a row number from the date. Consecutive dates produce the same result, and every gap changes it:

```sql
-- logins(user_id, login_date): login_date is a DATE, one row per user per day
-- (if a user can log in several times a day, use SELECT DISTINCT first)
WITH numbered AS (
  SELECT user_id,
         login_date,
         login_date - (ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ))::int AS grp
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;
```

Take one user who logged in on Jan 1, 2, 3, 5 and 6:

| login_date | row_number | login_date − row_number |
| --- | --- | --- |
| 2026-01-01 | 1 | 2025-12-31 |
| 2026-01-02 | 2 | 2025-12-31 |
| 2026-01-03 | 3 | 2025-12-31 |
| 2026-01-05 | 4 | 2026-01-01 |
| 2026-01-06 | 5 | 2026-01-01 |

Two distinct values means two islands: Jan 1 to 3 (3 days) and Jan 5 to 6 (2 days). The same trick finds runs of consecutive IDs, uninterrupted uptime, or winning streaks.

## Named windows: stop repeating yourself

When several columns share the same window, give it a name with the `WINDOW` clause:

```sql
SELECT id, region, amount,
       SUM(amount)   OVER w AS running_total,
       AVG(amount)   OVER w AS running_avg,
       COUNT(*)      OVER w AS sales_so_far
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date)
ORDER BY region, sale_date;
```

It's shorter, and it can't drift out of sync when you edit one copy and forget the others. (Supported in PostgreSQL, MySQL 8+, recent SQLite versions, and SQL Server 2022+.)

## Pitfalls to avoid

### You can't use a window function in WHERE

The database evaluates `WHERE` long before window functions run, so this fails:

```sql
-- ERROR: window functions are not allowed in WHERE
SELECT * FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) <= 2;
```

The logical order a query runs in looks like this:

![Mermaid Diagram](https://mermaid.ink/img/pako:eNo9z1FrwjAQB_CvcrvnFBnsqQ8D26aaoXakujEaH2KbaliaSBPnnPjdRyv4eH9-x__uirVrFMbYGneuD7IPsODCAgBMK4E5L5YwgbeCrQRuIYpeIakEfs4ppwK3d5iMeVoJnPFi8w7JF0xgPv1gq9nDpKPJhl1tG3eG9mTroJ31D5KNhFYCS7qg6RomkLFyzVbp-mHoaPJKYMEzyu9VC7ZkA0GCneo7qRuMrxgOqhsea1QrTyYguScfstdyZ5QfTOtsyGWnzQVjjOTxaFTkLz6ojkBitP1eyroc59zZQEBgqfZOwYYJJMDdzgVHYK7Mjwq6lgSmvZaGgJfWR171ukUylpT6b7jl-eX4i7cbwd0-dcb1GOPT-aCDwts_uuB2xg?type=png)

Window functions only see rows that survived `WHERE`, `GROUP BY` and `HAVING`. To filter on their results, wrap the query in a CTE or subquery, like the top-N example above. Snowflake, BigQuery, and DuckDB also offer a `QUALIFY` clause that does this in one step.

### NULL ordering differs by database

PostgreSQL treats `NULL` as larger than any value, so `ORDER BY x DESC` puts NULLs _first_. MySQL and SQLite treat `NULL` as smaller. Add `NULLS FIRST` or `NULLS LAST` (where supported) when ranking nullable columns.

### Performance

Window functions need sorted data. A few habits keep them fast:

-   Index the `PARTITION BY` and `ORDER BY` columns together, for example `(region, sale_date)`.
    
-   Reuse the **same** `OVER` definition where you can. PostgreSQL can compute several functions that share a window in one pass over one sort, while a window with a different `PARTITION BY` or `ORDER BY` may need a sort of its own.
    
-   Check the plan with `EXPLAIN ANALYZE`. In PostgreSQL, look for the `WindowAgg` node and any `Sort` feeding it.
    
-   Filter rows in `WHERE` first. Fewer rows entering the window means less sorting.
    

## Cheat sheet

| Function | What it does |
| --- | --- |
| ROW_NUMBER() | Unique sequential number per row |
| RANK() | Rank with gaps after ties |
| DENSE_RANK() | Rank without gaps |
| NTILE(n) | Splits the partition into n buckets |
| LAG(col, n, default) | Value from n rows before |
| LEAD(col, n, default) | Value from n rows after |
| FIRST_VALUE(col) / LAST_VALUE(col) | First / last value in the frame |
| NTH_VALUE(col, n) | The nth value in the frame |
| SUM / AVG / COUNT / MIN / MAX | Aggregate over the frame |
| PERCENT_RANK() / CUME_DIST() | Relative position of a row within its partition |

Everything except the aggregates only makes sense with an `ORDER BY` inside `OVER`.

## Wrapping up

Window functions boil down to one sentence: **do a calculation over a related set of rows, without losing the rows you started with.**

A good way to practise: take one of your own tables and try three tasks. Rank rows within a group, compute a running total, and compare each row to the previous one with `LAG`. After that, the rest of this post is just variations.

---

*Published via [ZyVOP](https://zyvop.com/sql-window-functions-explained-from-row-number-to-running-totals-35fbl?utm_source=hashnode&utm_medium=crosspost&utm_campaign=syndication) — Write once in Markdown, auto-backup to GitHub, and syndicate to Dev.to, Medium & Hashnode in 1 click.*
