LAG Function — Learn SQL

Overview

LAG() accesses data from a preceding row at a given offset within a partition, without needing a SELF JOIN.

Learning Objectives

Retrieve values from prior rows using LAG(column, offset, default).
Compute period-over-period differences (e.g. Month-over-Month growth).
Handle initial missing values using default fallback parameters.

Detailed Concept Explanation

LAG() looks back $N$ rows behind the current row within the window partition: LAG(column, offset, default_value)

column: The column value to retrieve.
offset (optional, default 1): How many rows back to look.
default_value (optional, default NULL): Value to return if the offset goes beyond the start of the partition.

Code Examples

SQL

sql

-- Calculate Month-over-Month sales difference
SELECT 
    sales_month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY sales_month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY sales_month) AS mom_growth
FROM monthly_sales;

Best Practices

Supply Default Parameter: Always provide a default parameter (e.g. LAG(col, 1, 0)) to prevent NULL values on the first row of each partition.

Interview Perspective

Note

Interview Question: How do you calculate Month-over-Month (MoM) revenue growth in SQL?

Use LAG(revenue, 1) OVER (ORDER BY month) to fetch the previous month's revenue, then subtract it from the current month's revenue.

Interactive Challenges

Challenge 1: Fetch Previous Amount

Select 'order_date', 'amount', and LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount from 'orders'.

Summary

LAG() looks back at previous rows in a partition, making period-over-period trend analysis easy.

Previous LessonDENSE_RANK

Next LessonLEAD Function