Ranking, LAG and LEAD, and window frames

Three ways to number rows

Customers ranked by how many orders they placed. Four customers tie on two orders each:

WITH per_customer AS (
  SELECT c.name, count(o.id) AS orders
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
)
SELECT name, orders,
       row_number() OVER (ORDER BY orders DESC) AS row_number,
       rank()       OVER (ORDER BY orders DESC) AS rank,
       dense_rank() OVER (ORDER BY orders DESC) AS dense_rank
FROM per_customer ORDER BY orders DESC, name;

    name    | orders | row_number | rank | dense_rank
------------+--------+------------+------+------------
 Asha Rao   |      3 |          1 |    1 |          1
 Ben Okafor |      2 |          4 |    2 |          2
 Chen Wei   |      2 |          5 |    2 |          2
 Divya Iyer |      2 |          2 |    2 |          2
 Farhan Ali |      2 |          3 |    2 |          2
 Grace Kim  |      1 |          6 |    6 |          3

Look at the row_number column: among the tied customers, Divya got 2 and Ben got 4. Nothing in the data says why; PostgreSQL broke the tie however it happened to. Run it on another day and the order can differ. Always add a tie-breaker when row_number must be repeatable: ORDER BY orders DESC, id.

LAG and LEAD: the row before, the row after

Days since each customer’s previous order:

SELECT c.name, o.id, o.ordered_at::date AS day,
       o.ordered_at::date
         - lag(o.ordered_at::date) OVER (PARTITION BY o.customer_id ORDER BY o.ordered_at)
         AS days_since_previous
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IN (1, 2)
ORDER BY c.name, o.ordered_at;

    name    | id  |    day     | days_since_previous
------------+-----+------------+---------------------
 Asha Rao   | 101 | 2025-03-02 |
 Asha Rao   | 103 | 2025-04-11 |                  40
 Asha Rao   | 108 | 2025-07-03 |                  83
 Ben Okafor | 102 | 2025-03-05 |
 Ben Okafor | 106 | 2025-06-01 |                  88

lag returns the value from the previous row in the window, or NULL for the first row; lead looks at the next row. Without window functions this needs a self join on “the latest earlier order”, which is much harder to read and to get right.

Frames, and the last_value surprise

        name         |  price  |    cheapest     | last_value (default frame)
---------------------+---------+-----------------+----------------------------
 Cable organiser     |  499.00 | Cable organiser | Cable organiser
 Wireless mouse      | 1200.00 | Cable organiser | Wireless mouse
 Laptop stand        | 1899.00 | Cable organiser | Laptop stand
 USB-C hub           | 2499.00 | Cable organiser | USB-C hub
 Mechanical keyboard | 4500.00 | Cable organiser | Mechanical keyboard

Widen the frame to the whole partition to get the real last value:

last_value(name) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

That returns “Mechanical keyboard” on every row.

Frames also give you moving windows. The average of each order and the two before it:

avg(order_total) OVER (ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Check yourself

1. Four customers tie for second place. What do rank() and dense_rank() give the next customer?

   Show answer

   rank() gives 6, skipping the positions the ties used. dense_rank() gives 3, with no gap.

   I could explain this without looking

2. Why can row_number() give different results for the same data on different runs?

   Show answer

   When rows tie on the window’s ORDER BY, their relative order is not defined. Add a unique tie-breaker column, such as the id, to make it repeatable.

   I could explain this without looking

3. Why does last_value(name) OVER (ORDER BY price) return the current row’s name?

   Show answer

   With ORDER BY, the default frame ends at the current row (and its ties), so the last row of the frame is the current one. Widen it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

   I could explain this without looking