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

A cycling club keeps one row per ride and wants three extra columns on the same listing: which ride this was for that rider, the rider's running distance so far, and how much longer or shorter the ride was than the previous one. GROUP BY cannot do it, because grouping collapses many rows into one. You would need a self-join or a loop in application code. A window function does the job in one pass: it computes a value from a set of related rows (the window) and attaches it to each row without removing any.

The shape of a window: OVER, PARTITION BY, ORDER BY

Any function followed by OVER (...) is evaluated as a window function. Inside the parentheses, PARTITION BY splits rows into independent groups (like GROUP BY, but nothing collapses) and ORDER BY sets the sequence inside each partition. Window functions are available in SQLite from version 3.25, in MySQL from 8.0 and in PostgreSQL and SQL Server for much longer, so the syntax below is portable except where noted. These queries ran against SQLite.

CREATE TABLE rides (
  ride_id   INTEGER PRIMARY KEY,
  rider     TEXT    NOT NULL,
  ride_date TEXT    NOT NULL,   -- ISO date
  km        INTEGER NOT NULL
);

INSERT INTO rides (rider, ride_date, km) VALUES
  ('Asha',  '2025-06-01', 42),
  ('Asha',  '2025-06-04', 30),
  ('Asha',  '2025-06-04', 18),
  ('Asha',  '2025-06-09', 55),
  ('Bruno', '2025-06-02', 30),
  ('Bruno', '2025-06-05', 64),
  ('Bruno', '2025-06-08', 30),
  ('Chen',  '2025-06-01', 25),
  ('Chen',  '2025-06-03', 64),
  ('Chen',  '2025-06-10', 12);

ROW_NUMBER() simply numbers the rows of each partition in the given order. Note the extra ride_id in the window's ORDER BY: Asha rode twice on 4 June, and without a tiebreaker the database may number those two rows in either order.

SELECT rider, ride_date, km,
       ROW_NUMBER() OVER (
         PARTITION BY rider
         ORDER BY ride_date, ride_id
       ) AS nth_ride
FROM rides
ORDER BY rider, ride_date, ride_id;

rider | ride_date  | km | nth_ride
------+------------+----+---------
Asha  | 2025-06-01 | 42 |        1
Asha  | 2025-06-04 | 30 |        2
Asha  | 2025-06-04 | 18 |        3
Asha  | 2025-06-09 | 55 |        4
Bruno | 2025-06-02 | 30 |        1
Bruno | 2025-06-05 | 64 |        2
Bruno | 2025-06-08 | 30 |        3
Chen  | 2025-06-01 | 25 |        1
Chen  | 2025-06-03 | 64 |        2
Chen  | 2025-06-10 | 12 |        3

ROW_NUMBER, RANK and DENSE_RANK disagree about ties

All three assign positions, but they treat equal values differently. The next query ranks every ride by distance, longest first. Two rides cover 64 km, and three cover 30 km.

SELECT ride_id, rider, km,
       ROW_NUMBER() OVER (ORDER BY km DESC, ride_id) AS row_num,
       RANK()       OVER (ORDER BY km DESC)          AS rnk,
       DENSE_RANK() OVER (ORDER BY km DESC)          AS dense_rnk
FROM rides
ORDER BY km DESC, ride_id;

ride_id | rider | km | row_num | rnk | dense_rnk
--------+-------+----+---------+-----+----------
      6 | Bruno | 64 |       1 |   1 |         1
      9 | Chen  | 64 |       2 |   1 |         1
      4 | Asha  | 55 |       3 |   3 |         2
      1 | Asha  | 42 |       4 |   4 |         3
      2 | Asha  | 30 |       5 |   5 |         4
      5 | Bruno | 30 |       6 |   5 |         4
      7 | Bruno | 30 |       7 |   5 |         4
      8 | Chen  | 25 |       8 |   8 |         5
      3 | Asha  | 18 |       9 |   9 |         6
     10 | Chen  | 12 |      10 |  10 |         7

Read the three right-hand columns of the output above. Both 64 km rides are rank 1, then RANK jumps to 3 for the 55 km ride, while DENSE_RANK continues at 2 and ROW_NUMBER just keeps counting. The three rides of 30 km all hold RANK 5, and the next ride (25 km) is RANK 8.

Running totals, and the default-frame trap

Add an aggregate such as SUM to a window with an ORDER BY and you get a running total. The subtlety is the frame, the slice of the partition that each row sees. If you give an ORDER BY but no frame, the standard default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats rows with equal sort values as a single unit. Watch what happens to Asha's two rides on 4 June.

SELECT ride_date, km,
       SUM(km) OVER (ORDER BY ride_date)                       AS running_default,
       SUM(km) OVER (ORDER BY ride_date, ride_id
                     ROWS BETWEEN UNBOUNDED PRECEDING
                              AND CURRENT ROW)                 AS running_rows
FROM rides
WHERE rider = 'Asha'
ORDER BY ride_date, ride_id;

ride_date  | km | running_default | running_rows
-----------+----+-----------------+-------------
2025-06-01 | 42 |              42 |           42
2025-06-04 | 30 |              90 |           72
2025-06-04 | 18 |              90 |           90
2025-06-09 | 55 |             145 |          145

In running_default both 4 June rides show 90, because they are peers and each sees the other. The correct running total, with the tie broken by ride_id and a ROWS frame, goes 42, 72, 90, 145. The default is harmless when the order column is unique and misleading when it is not, so write the frame explicitly (and a unique tiebreaker) whenever the numbers matter. The same ROWS machinery gives moving averages: change the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

SELECT rider, ride_date, km,
       ROUND(AVG(km) OVER (
         PARTITION BY rider
         ORDER BY ride_date, ride_id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_last_3
FROM rides
ORDER BY rider, ride_date, ride_id;

rider | ride_date  | km | avg_last_3
------+------------+----+-----------
Asha  | 2025-06-01 | 42 |       42.0
Asha  | 2025-06-04 | 30 |       36.0
Asha  | 2025-06-04 | 18 |       30.0
Asha  | 2025-06-09 | 55 |       34.3
Bruno | 2025-06-02 | 30 |       30.0
Bruno | 2025-06-05 | 64 |       47.0
Bruno | 2025-06-08 | 30 |       41.3
Chen  | 2025-06-01 | 25 |       25.0
Chen  | 2025-06-03 | 64 |       44.5
Chen  | 2025-06-10 | 12 |       33.7

Each row averages itself and up to two earlier rides of the same rider. Early rows have fewer than three rides available, so their averages use fewer values, which is easy to forget when charting.

LAG and LEAD: looking at neighbouring rows

LAG(col) returns the value from the previous row in the window's order, and LEAD(col) the next one. The first and last rows of a partition have no neighbour, so they get NULL (you can supply a default as a second argument). When you reuse the same window several times, name it with a WINDOW clause instead of repeating it.

SELECT rider, ride_date, km,
       LAG(km)         OVER w AS prev_km,
       km - LAG(km)    OVER w AS change,
       CAST(julianday(ride_date) - julianday(LAG(ride_date) OVER w) AS INTEGER) AS days_gap,
       LEAD(ride_date) OVER w AS next_ride
FROM rides
WINDOW w AS (PARTITION BY rider ORDER BY ride_date, ride_id)
ORDER BY rider, ride_date, ride_id;

rider | ride_date  | km | prev_km | change | days_gap | next_ride
------+------------+----+---------+--------+----------+-----------
Asha  | 2025-06-01 | 42 |    NULL |   NULL |     NULL | 2025-06-04
Asha  | 2025-06-04 | 30 |      42 |    -12 |        3 | 2025-06-04
Asha  | 2025-06-04 | 18 |      30 |    -12 |        0 | 2025-06-09
Asha  | 2025-06-09 | 55 |      18 |     37 |        5 | NULL
Bruno | 2025-06-02 | 30 |    NULL |   NULL |     NULL | 2025-06-05
Bruno | 2025-06-05 | 64 |      30 |     34 |        3 | 2025-06-08
Bruno | 2025-06-08 | 30 |      64 |    -34 |        3 | NULL
Chen  | 2025-06-01 | 25 |    NULL |   NULL |     NULL | 2025-06-03
Chen  | 2025-06-03 | 64 |      25 |     39 |        2 | 2025-06-10
Chen  | 2025-06-10 | 12 |      64 |    -52 |        7 | NULL

Asha's second ride was 12 km shorter than the first and three days later; her third ride the same day gives a gap of 0. Each rider's first ride has NULLs for the previous-ride columns. The date arithmetic uses SQLite's julianday; in PostgreSQL you would subtract the dates directly, and in MySQL use DATEDIFF.

Top row per group: window in a subquery

Window functions are computed after WHERE, so you cannot filter on them in the same query level.

SELECT rider, km FROM rides WHERE ROW_NUMBER() OVER (PARTITION BY rider ORDER BY km DESC) = 1;

Error: misuse of window function ROW_NUMBER()

Wrap the ranking in a subquery (or a CTE, covered in the next lesson) and filter outside it. This is the standard recipe for "latest row per customer" or "top three per category".

SELECT rider, ride_date, km
FROM (
  SELECT rider, ride_date, km,
         ROW_NUMBER() OVER (PARTITION BY rider ORDER BY km DESC, ride_date) AS rn
  FROM rides
) AS ranked
WHERE rn = 1
ORDER BY rider;

rider | ride_date  | km
------+------------+---
Asha  | 2025-06-09 | 55
Bruno | 2025-06-05 | 64
Chen  | 2025-06-03 | 64

Chen and Bruno both rode 64 km once. Because the window orders by km DESC, ride_date, any tie between a rider's own equal distances would resolve to the earlier date, and ROW_NUMBER guarantees exactly one row per rider. If you wanted every tied row, use RANK() = 1 instead.

Common mistakes

• Filtering on a window function in WHERE. Error: misuse of window function ROW_NUMBER() in SQLite, or window functions are not allowed in WHERE in PostgreSQL. Move the ranking into a subquery.
• No tiebreaker in ROW_NUMBER or running totals. Symptom: results that change between runs, or running totals where tied rows show the same figure. Add a unique column to the ORDER BY.
• Relying on the default frame. Symptom: a running total that jumps for peer rows, or LAST_VALUE returning the current row instead of the last one. State the frame explicitly.
• Confusing PARTITION BY with GROUP BY. A partition does not collapse rows. If your output has fewer rows than you wanted, you grouped; if it has more, you windowed.
• Forgetting the outer ORDER BY. The window's ORDER BY does not sort the final result. Add your own ORDER BY at the end.

How this shows up in real projects and interviews

Leaderboards, month-over-month change, running balances, de-duplication (keep the latest row per key) and sessionising events all use window functions, and they are a staple of data-analyst and backend interviews. The usual follow-ups are "what is the difference between RANK and DENSE_RANK", "find the second-highest per group" and "why did my running total repeat", all covered above. Window queries often sort the same data several ways, so indexes that match the PARTITION BY and ORDER BY columns can matter at scale; see Database Indexing. If you need to refresh grouping or joins first, start with SQL Fundamentals.