{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"sql-window-functions-explained-from-row-number-to-running-totals-35fbl","url":"https://zyvop.com/sql-window-functions-explained-from-row-number-to-running-totals-35fbl","title":"SQL Window Functions Explained: From ROW_NUMBER to Running Totals","subtitle":"A hands-on guide to ranking, running totals, LAG/LEAD, moving averages and top-N per group, with runnable PostgreSQL examples and outputs.","tldr":"GROUP BY collapses your rows; window functions don't. Learn how OVER, PARTITION BY, ORDER BY and frames work, then use them on everyday problems like deduplication, consecutive-day streaks and month-over-month change.","keywords":["Window Functions","postgresql","SQL","data-analysis","databases","Databases"],"entities":["Anshu Pathak","Window Functions","postgresql","SQL","data-analysis","databases","Databases","ZyVOP"],"keyTakeaways":["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."],"headings":["The one idea: keep the rows, add a calculation","Anatomy of an OVER clause","Aggregates as window functions","Ranking: ROW_NUMBER, RANK, DENSE_RANK","Top-N per group","Looking at neighbouring rows: LAG and LEAD","Running totals and moving averages","Understanding frames","The LAST_VALUE gotcha","ROWS vs RANGE","Two real-world recipes","1. Deduplicate: keep the latest row per key","2. Gaps and islands: find consecutive streaks","Named windows: stop repeating yourself","Pitfalls to avoid","You can't use a window function in WHERE","NULL ordering differs by database","Performance","Cheat sheet","Wrapping up"],"outboundLinks":[],"contentText":"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: 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: 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: 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 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: flowchart LR A[\"Rows left after&lt;br/&gt;FROM, WHERE, GROUP BY\"] --&gt; B[\"PARTITION BY&lt;br/&gt;split into groups\"] B --&gt; C[\"ORDER BY&lt;br/&gt;sort inside each group\"] C --&gt; D[\"Frame&lt;br/&gt;rows visible to the current row\"] D --&gt; E[\"Function runs&lt;br/&gt;SUM, RANK, LAG ...\"] E --&gt; F[\"Result attached to the row&lt;br/&gt;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: 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: 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: 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 &lt;= 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: 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: 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: No ORDER BY in the OVER clause: the frame is the whole partition. 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: 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. 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: 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 &gt; 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: -- 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: 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: -- ERROR: window functions are not allowed in WHERE SELECT * FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) &lt;= 2;The logical order a query runs in looks like this: flowchart LR A[\"FROM / JOIN\"] --&gt; B[\"WHERE\"] B --&gt; C[\"GROUP BY / HAVING\"] C --&gt; D[\"Window functions\"] D --&gt; E[\"SELECT / DISTINCT\"] E --&gt; 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.","contentHash":"sha256:97ee9e724ddec0c6c51f6fc7fe88b413ab6a9bb0f3944c16a1884b1a305eeb9a","authorName":"Anshu Pathak","authorUrl":"https://zyvop.com/author/anshu","authorSameAs":["https://www.linkedin.com/in/anshu-pathak-zyvop/"],"category":"Databases","tags":["Window Functions","postgresql","SQL","data-analysis","databases"],"audience":"Software engineers and developers building applications with Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":10,"wordCount":2457,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-11-19T05:15:52.261Z","updatedAt":"2026-11-19T05:15:52.261Z","canonicalUrl":"https://zyvop.com/sql-window-functions-explained-from-row-number-to-running-totals-35fbl"}