LAG, LEAD and running totals

LAG and LEAD are two special window functions that give you access to other rows of the same partition: the previous row (LAG) or the next one (LEAD) according to the window's ORDER BY. They are the go-to tool for row-to-row comparisons (deltas, variations, intervals) and — combined with SUM/AVG/COUNT OVER — for running totals.

LAG: the previous row

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

Example: for each order, "days since the previous order by the same customer":

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

Each customer's first order has since_previous_order = NULL because there is no "previous" in the partition.

LEAD: the next row

The mirror of LAG. Typical: "time until the next event", "difference with the next version".

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

Running totals: aggregates with OVER

Aggregate functions (SUM, AVG, COUNT, MAX, MIN) become cumulative when combined with OVER (… ORDER BY …):

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

For each row, SUM computes the sum of all rows in the partition up to that point, according to the window's ORDER BY. The result is the classic "running balance".

Try it

Review exercise