SQL Window Functions: ROW_NUMBER, RANK, LAG, and Running Totals

GROUP BY is great until you want a total next to each row, not instead of the rows. Say you want each sale listed, along with the total for that salesperson. Or the rank of each sale within a region. Plain GROUP BY can't do that without awkward self-joins.

Window functions can. They calculate something across a set of related rows, but keep every row in the result.

The sample data

Run this in the SQL editor.

CREATE TABLE sales (id INTEGER PRIMARY KEY, rep TEXT, region TEXT, sale_date TEXT, amount INTEGER);

INSERT INTO sales (rep, region, sale_date, amount) VALUES
  ('Ana', 'East', '2025-01-05', 300),
  ('Ana', 'East', '2025-01-20', 500),
  ('Ana', 'East', '2025-02-10', 200),
  ('Ben', 'East', '2025-01-12', 400),
  ('Ben', 'East', '2025-02-02', 400),
  ('Cy',  'West', '2025-01-08', 700),
  ('Cy',  'West', '2025-02-15', 100),
  ('Dee', 'West', '2025-01-25', 250);

GROUP BY versus a window

First, the familiar way. GROUP BY collapses rows:

SELECT rep, SUM(amount) AS total
FROM sales
GROUP BY rep
ORDER BY rep;

rep  total
---  -----
Ana  1000
Ben  800
Cy   800
Dee  250

Four rows, one per rep. Now the window version, using OVER:

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

rep  sale_date   amount  rep_total
---  ----------  ------  ---------
Ana  2025-01-05  300     1000
Ana  2025-01-20  500     1000
Ana  2025-02-10  200     1000
Ben  2025-01-12  400     800
Ben  2025-02-02  400     800
Cy   2025-01-08  700     800
Cy   2025-02-15  100     800
Dee  2025-01-25  250     250

All eight rows are still here, and each one carries its rep's total. OVER (...) is what turns an ordinary function into a window function. PARTITION BY rep says which rows form a group. Think of it as GROUP BY that doesn't remove rows.

You can now do math between the row and its group, such as each sale's share of the rep's total:

SELECT rep, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY rep), 1) AS pct_of_rep
FROM sales
ORDER BY rep, sale_date;

rep  amount  pct_of_rep
---  ------  ----------
Ana  300     30.0
Ana  500     50.0
Ana  200     20.0
Ben  400     50.0
Ben  400     50.0
Cy   700     87.5
Cy   100     12.5
Dee  250     100.0

ROW_NUMBER, RANK, and DENSE_RANK

These three number rows within each partition, according to an ORDER BY inside OVER.

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

rep  amount  row_num  rnk  dense_rnk
---  ------  -------  ---  ---------
Cy   700     1        1    1
Ana  500     2        2    2
Ben  400     3        3    3
Ben  400     4        3    3
Ana  300     5        5    4
Dee  250     6        6    5
Ana  200     7        7    6
Cy   100     8        8    7

Look at Ben's two 400 sales. ROW_NUMBER always gives unique numbers, so tied rows get different ones (3 and 4). RANK gives ties the same rank and then skips numbers, so after two 3rds comes 5th. DENSE_RANK gives ties the same rank without skipping, so the next one is 4th.

Top N per group

This is one of the most common real-world uses: the best sale in each region. Rank within each partition, then keep rank 1. You can't filter on a window function directly, so wrap it in a subquery (or a CTE).

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

region  rep  amount
------  ---  ------
East    Ana  500
West    Cy   700

Change rn = 1 to rn <= 2 for the top two in each region. Use RANK instead of ROW_NUMBER if you want ties to all count.

LAG and LEAD: look at neighboring rows

LAG reads the previous row, and LEAD reads the next one. They're perfect for comparing a value to the one before it.

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

rep  sale_date   amount  previous_amount  change
---  ----------  ------  ---------------  ------
Ana  2025-01-05  300     NULL             NULL
Ana  2025-01-20  500     300              200
Ana  2025-02-10  200     500              -300
Ben  2025-01-12  400     NULL             NULL
Ben  2025-02-02  400     400              0
Cy   2025-01-08  700     NULL             NULL
Cy   2025-02-15  100     700              -600
Dee  2025-01-25  250     NULL             NULL

The first sale for each rep has no previous row, so you get NULL. You can pass a default as the third argument: LAG(amount, 1, 0).

Running totals

Add an ORDER BY to a SUM window and it accumulates as it goes.

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

sale_date   amount  running_total
----------  ------  -------------
2025-01-05  300     300
2025-01-08  700     1000
2025-01-12  400     1400
2025-01-20  500     1900
2025-01-25  250     2150
2025-02-02  400     2550
2025-02-10  200     2750
2025-02-15  100     2850

Each row's total includes everything up to and including that row. Notice that we added id to the order, so rows on the same date are processed in a predictable order.

Moving averages with a frame

You can control exactly which neighboring rows are included using a frame clause.

SELECT sale_date, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY sale_date, id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_last_3
FROM sales
ORDER BY sale_date, id;

sale_date   amount  avg_last_3
----------  ------  ----------
2025-01-05  300     300.0
2025-01-08  700     500.0
2025-01-12  400     466.7
2025-01-20  500     533.3
2025-01-25  250     383.3
2025-02-02  400     383.3
2025-02-10  200     283.3
2025-02-15  100     233.3

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it." The first rows have fewer than three to average, so they use whatever is available.

Common mistakes

Using a window function in WHERE. The database evaluates WHERE before windows, so this fails. Wrap the query in a subquery or CTE, as in the top-N example.

Forgetting ORDER BY inside OVER for ranking or LAG. Without it, "previous" has no meaning.

Mixing up PARTITION BY and ORDER BY. Partition splits rows into groups. Order sorts them inside each group. A running total per rep needs both.

Expecting the outer ORDER BY to change the window. The final ORDER BY only sorts the output. The one inside OVER defines the calculation.

Key takeaways

• A window function calculates across related rows but keeps every row.
• PARTITION BY defines the groups, ORDER BY inside OVER defines the sequence.
• ROW_NUMBER, RANK, and DENSE_RANK differ only in how they treat ties.
• LAG/LEAD compare to neighbors, and SUM() OVER (ORDER BY ...) gives running totals.
• To filter on a window result, wrap the query in a subquery or CTE.