PARTITION BY with LAG and LEAD
What you'll learn: How to use PARTITION BY with LAG and LEAD to access previous or next rows within separate groups, resetting row access at each partition boundary.
The Core Idea
When you use LAG or LEAD without PARTITION BY, these functions scan through your entire result set linearly. But often, you want to compare rows only within logical groups—like tracking each customer's order history separately, or comparing monthly sales within each product category.
Adding PARTITION BY creates invisible "fences" between groups. LAG and LEAD respect these boundaries: they'll never reach across partitions to grab data from a different group.
Think of it like lanes on a highway: Without partitions, you're comparing your car to every vehicle on the road. With PARTITION BY, you only compare to cars in your lane—the partition resets at each lane boundary.
How It Works in Practice
The syntax combines what you already know:
LAG(column_name, offset, default)
OVER (PARTITION BY grouping_column ORDER BY sort_column)
LEAD(column_name, offset, default)
OVER (PARTITION BY grouping_column ORDER BY sort_column)
For example, comparing each employee's salary to their previous salary within their department:
SELECT
department,
employee_name,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_dept_salary
FROM employees;
Without PARTITION BY department, the first employee in Marketing might compare to the last employee in Sales—meaningless! The partition ensures comparisons stay within the same department.
Key Takeaway: PARTITION BY with LAG and LEAD resets row access at group boundaries, ensuring you only compare rows within the same logical partition—essential for meaningful period-over-period or sequential analysis within categories.