LEAD & LAG
Overview
LEAD and LAG are window functions that let you fetch data from subsequent (LEAD) or previous (LAG) rows within a partition, based on a specific order. You can optionally pass offset and default arguments, like `LAG(column, offset, default_value)`, to look multiple rows back or provide a fallback if no row exists (e.g., `LAG(salary, 1, 0)` looks 1 row back and defaults to 0). This lets you compare the current row to an adjacent row without using a self-join.
Crucial for calculating day-over-day growth, checking if an order's status changed since the last order, or analyzing consecutive events.
Where used: time-series analysis, financial reporting
Why learn this
- Compute time intervals between events (like days between purchases).
- Detect state changes in logs without complex self-joins.
Query walkthrough
SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_order FROM orders WHERE customer_id IN (1, 2) ORDER BY customer_id, order_date;Focus: Notice how the LAG calculation restarts for each customer_id because of the PARTITION BY clause.
Common mistakes
- Forgetting the ORDER BY in the OVER clause: LEAD and LAG require an ORDER BY in the OVER clause to determine what is considered 'previous' or 'next'.
Glossary
- subsequent
- Coming after something in time or order; the next item in a sequence.
- adjacent
- Next to or adjoining something else; rows that are immediately before or after each other.
- consecutive
- Following continuously without interruption, one right after the other.
Recall questions
- What happens if LAG() is called on the first row of a partition?
Understanding checks
What rows does this query return?
1 | 2023-02-01 | 2 2 | 2023-03-15 | NULL
Customer 1 has orders 1 and 2. LEAD(id) gets the id of the next row. For the last row, there is no next row, so it returns NULL.
What rows does this query return?
Yara | 90000 | 0 Xavier | 95000 | 90000 Wendy | 130000 | 95000
LAG fetches the salary from the previous row based on salary order. The third argument (0) provides a default value for the first row instead of NULL.
Practice tasks
Get the previous order status
Modify the query to include a column named `prev_status` using LAG to show the status of the previous order in order_date sequence.
Challenge
Find the next lower salary
Write a query returning name, salary, and the salary of the next lower paid employee (using LEAD). Order by salary descending. Filter for the Engineering department.
Continue learning
Previous: OVER & PARTITION BY