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

LAG Function: Accessing Previous Row Values

Learn to use LAG(column, offset, default) to retrieve values from preceding rows in ordered data.

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.