Course contentsShow
← Data Science
Lesson 1028 of 2,14526. Advanced SQLFree lesson

PARTITION BY with LAG and LEAD

Apply LAG and LEAD within groups using PARTITION BY to reset row access at partition boundaries.

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.