--- title: "SQL Window Functions Explained: From ROW_NUMBER to Running Totals" author: "Anshu Pathak" published: "2026-11-19T05:15:52.261Z" updated: "2026-11-19T05:15:52.261Z" url: "https://zyvop.com/sql-window-functions-explained-from-row-number-to-running-totals-35fbl" canonical: "https://zyvop.com/sql-window-functions-explained-from-row-number-to-running-totals-35fbl" tags: ["Window Functions","postgresql","SQL","data-analysis","databases"] --- # SQL Window Functions Explained: From ROW_NUMBER to Running Totals A hands-on guide to ranking, running totals, LAG/LEAD, moving averages and top-N per group, with runnable PostgreSQL examples and outputs. ![Cover image]() 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 flowchart LR A["Rows left after
FROM, WHERE, GROUP BY"] --> B["PARTITION BY
split into groups"] B --> C["ORDER BY
sort inside each group"] C --> D["Frame
rows visible to the current row"] D --> E["Function runs
SUM, RANK, LAG ..."] E --> F["Result attached to the row
no rows are removed"] ``` ## 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 flowchart LR A["FROM / JOIN"] --> B["WHERE"] B --> C["GROUP BY / HAVING"] C --> D["Window functions"] D --> E["SELECT / DISTINCT"] E --> F["ORDER BY / LIMIT"] ``` 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.