In this lesson, you will learn how to look at the "neighbors" of a data row to calculate changes between consecutive time periods.
Why look sideways?
Until now, our work with window functions focused on aggregating data across a range—like a running total. However, to calculate performance metrics like "month-over-month growth," we don't need a sum; we need to compare a specific cell to the one immediately before or after it. In SQL, we use LAG() to peek at the previous record and LEAD() to peek at the next record within an ordered partition.
The Mechanics
Think of your dataset as a line of people standing in a specific order (defined by your ORDER BY clause). If you are standing in line, LAG() asks the person directly behind you for their value, while LEAD() asks the person directly in front of you.
Consider a table of monthly sales:
SELECT
sale_month,
sales_amount,
LAG(sales_amount) OVER (ORDER BY sale_month) AS previous_month_sales
FROM store_performance;
When the database engine executes this, it doesn't need a self-join. Because of the Partitioning Versus Ordering Dynamics we discussed previously, the query engine uses the existing sort order to retrieve the adjacent record in a single pass.
Calculating Performance Changes
Once you have isolated these observations, calculating the change is straightforward arithmetic. If you want to find the growth percentage, you simply perform the subtraction: (sales_amount - previous_month_sales) / previous_month_sales.
Because these functions are non-blocking, the engine treats these "looks" as a simple memory offset during the windowing phase of the plan. It avoids the heavy overhead of nested loops or Cartesian products, making it the most efficient way to track trends across your time-series data.
Remember, if there is no previous record (like in the very first row), the function returns NULL. You can handle these missing edges with specific logic, but the structural power remains: you are now comparing points in time rather than just collapsing them into a single aggregate value.
Key Takeaway: Use LAG and LEAD to access adjacent row values without self-joins, enabling efficient period-over-period calculations within your analytical frames.