LAG, LEAD, FIRST_VALUE and LAST_VALUE

Concept

Offset functions let a row see other rows in its window without a self join. They answer questions about sequence: what changed since last month, how long between events, how does this row compare to the best in its group.

offset defaults to 1 and default to NULL.

Example

SELECT id,
       order_date,
       total,
       LAG(total)  OVER (ORDER BY order_date)          AS previous_total,
       LEAD(total) OVER (ORDER BY order_date)          AS next_total,
       total - LAG(total) OVER (ORDER BY order_date)   AS change_vs_previous,
       DATEDIFF(order_date,
                LAG(order_date) OVER (ORDER BY order_date)) AS days_since_previous
FROM   orders
ORDER BY order_date;

-- Per customer: the gap between each customer's own consecutive orders
SELECT customer_id,
       order_date,
       total,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       DATEDIFF(order_date,
                LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))
                                                                            AS days_between
FROM   orders
ORDER BY customer_id, order_date;

Explanation

The first row of each partition has no previous row, so LAG returns NULL and the difference is NULL too. Supply a default when zero is more useful than blank:

LAG(total, 1, 0) OVER (ORDER BY order_date)   -- 0 instead of NULL for the first row

The LAST_VALUE trap

-- Looks right, is wrong: returns the CURRENT row's value, not the window's last
SELECT first_name, dept_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS highest,
       LAST_VALUE(salary)  OVER (PARTITION BY dept_id ORDER BY salary DESC) AS lowest_wrong
FROM   employees;

-- Correct: state the frame explicitly
SELECT first_name, dept_id, salary,
       FIRST_VALUE(salary) OVER w AS highest,
       LAST_VALUE(salary)  OVER w AS lowest
FROM   employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

The cause is the default frame. With ORDER BY present, the frame is start of partition to current row - so the "last value" seen so far is always the current row. FIRST_VALUE is unaffected because the frame always starts at the partition start. Whenever you use LAST_VALUE, write the frame.

Detecting changes

-- Flag every row where the status differs from the previous one for that customer
SELECT customer_id, order_date, status,
       LAG(status) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_status,
       CASE WHEN status <> LAG(status) OVER (PARTITION BY customer_id ORDER BY order_date)
            THEN 1 ELSE 0 END AS status_changed
FROM   orders
ORDER BY customer_id, order_date;

Important rules

• LAG and LEAD require ORDER BY inside OVER.
• They ignore the frame clause; only the ordering matters.
• Out of range offsets return the default, or NULL if none was given.
• LAST_VALUE and NTH_VALUE are frame sensitive; FIRST_VALUE is not, under the default frame.
• These functions see the rows of the window, not the physical table order - so the window ORDER BY is the definition of "previous".

Common mistakes

• Using LAST_VALUE without a frame and getting the current row.
• Forgetting PARTITION BY, so the "previous order" comes from a different customer.
• Not handling the NULL on the first row of each partition.
• Ordering by a non unique column, so "previous" is ambiguous between tied rows.

Best practices

• Define the window once with a WINDOW clause when it is used more than twice.
• Always write the frame explicitly when using LAST_VALUE or NTH_VALUE.
• Supply a sensible default to LAG and LEAD rather than filtering NULLs later.
• Include a unique tie breaker in the window ORDER BY.

Practice

1. For each order, show the change in total from that customer's previous order.
2. Show each employee with the highest and lowest salary in their department on the same row.
3. Explain why LAST_VALUE needs an explicit frame but FIRST_VALUE usually does not.