LAG Function: Accessing Previous Row Values
You'll learn how to use the LAG function to retrieve values from previous rows within an ordered dataset.
What is LAG?
The LAG function is a window function that lets you "look backward" at earlier rows in your result set. Think of it like reading a book where you can flip back one (or more) pages to check what happened before—except you're doing this with table rows.
Core Syntax
LAG(column_name, offset, default_value) OVER (ORDER BY sort_column)
- column_name: The column whose previous value you want to retrieve
- offset: How many rows back to look (default is 1)
- default_value: What to return if there's no previous row (default is NULL)
How It Works
When you apply LAG to ordered data, each row can access values from rows that came before it in the ordering. The first row has no predecessor, so it returns the default value.
SELECT
date,
sales,
LAG(sales, 1, 0) OVER (ORDER BY date) AS previous_day_sales
FROM daily_sales;
This query shows today's sales alongside yesterday's sales in the same row—perfect for day-over-day comparisons.
Common Use Cases
- Change detection: Compare current vs. previous values (e.g.,
sales - LAG(sales)) - Gap analysis: Find when values changed from the prior period
- Sequence validation: Check if values follow expected patterns
Key Takeaway: LAG(column, offset, default) retrieves values from preceding rows based on your specified ordering, enabling row-to-row comparisons without self-joins.