If you have ever needed to compare a value in one row to the value in the previous row i.e. calculating month-over-month sales growth, finding the difference between consecutive readings, or identifying how long something took between events — you have encountered exactly the problem the SQL LAG function was built to solve.
Before LAG existed, analysts had to write complex self-joins or correlated subqueries to access data from a previous row. LAG makes this elegant, readable, and efficient in a single function call.
In this guide, we will break down the SQL LAG function with a real dataset — walking through every parameter, building up from simple to complex examples, and covering the most common real-world use cases.
What Is the LAG Function?
LAG is a window function that gives you access to a value from a previous row in the same result set without needing a self-join.
It looks back a specified number of rows and returns the value of a column from that earlier row, based on the ordering you define.
Simple Analogy
Imagine you are reading a spreadsheet of monthly sales figures. You want to know how much sales changed compared to last month. You look at the current row’s value, then glance up one row to see last month’s value, and subtract.
LAG does exactly that. It automatically “glances up” however many rows you specify and hands you the value from that row.
LAG Function Syntax
sql
LAG(column, offset, default_value)
OVER (PARTITION BY partition_column ORDER BY order_column)- column — The column whose previous value you want to retrieve
- offset — How many rows back to look (default is 1)
- default_value — What to return when there is no previous row (default is NULL)
- PARTITION BY — Optional. Restarts the window for each group
- ORDER BY — Required. Defines what “previous” means
Setting Up the Real Dataset
We will use a realistic monthly sales dataset throughout this guide.
sql
CREATE TABLE monthly_sales (
sale_id INT,
region VARCHAR(50),
month_date DATE,
revenue DECIMAL(12, 2),
units_sold INT
);
INSERT INTO monthly_sales VALUES
(1, 'North', '2024-01-01', 125000, 520),
(2, 'North', '2024-02-01', 138000, 580),
(3, 'North', '2024-03-01', 142000, 610),
(4, 'North', '2024-04-01', 131000, 545),
(5, 'North', '2024-05-01', 155000, 650),
(6, 'North', '2024-06-01', 162000, 680),
(7, 'South', '2024-01-01', 98000, 410),
(8, 'South', '2024-02-01', 104000, 435),
(9, 'South', '2024-03-01', 97000, 405),
(10, 'South', '2024-04-01', 112000, 468),
(11, 'South', '2024-05-01', 119000, 497),
(12, 'South', '2024-06-01', 128000, 535);Table Preview:
| sale_id | region | month_date | revenue | units_sold |
|---|---|---|---|---|
| 1 | North | 2024-01-01 | 125000 | 520 |
| 2 | North | 2024-02-01 | 138000 | 580 |
| 3 | North | 2024-03-01 | 142000 | 610 |
| 4 | North | 2024-04-01 | 131000 | 545 |
| 5 | North | 2024-05-01 | 155000 | 650 |
| 6 | North | 2024-06-01 | 162000 | 680 |
| 7 | South | 2024-01-01 | 98000 | 410 |
| … | … | … | … | … |
Example 1: Basic LAG — Previous Month Revenue
The simplest use — retrieve last month’s revenue alongside the current month.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (ORDER BY month_date) AS prev_month_revenue
FROM monthly_sales
WHERE region = 'North'
ORDER BY month_date;Result:
| region | month_date | revenue | prev_month_revenue |
|---|---|---|---|
| North | 2024-01-01 | 125000 | NULL |
| North | 2024-02-01 | 138000 | 125000 |
| North | 2024-03-01 | 142000 | 138000 |
| North | 2024-04-01 | 131000 | 142000 |
| North | 2024-05-01 | 155000 | 131000 |
| North | 2024-06-01 | 162000 | 155000 |
January returns NULL because there is no previous row. It is the first month in the dataset.
Example 2: Replacing NULL With a Default Value
The third argument of LAG provides a default value when no previous row exists.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month_date) AS prev_month_revenue
FROM monthly_sales
WHERE region = 'North'
ORDER BY month_date;Result:
| region | month_date | revenue | prev_month_revenue |
|---|---|---|---|
| North | 2024-01-01 | 125000 | 0 |
| North | 2024-02-01 | 138000 | 125000 |
| North | 2024-03-01 | 142000 | 138000 |
January now shows 0 instead of NULL. This is useful when you need to perform arithmetic on the result without NULL propagation breaking your calculations.
Example 3: Calculating Month-Over-Month Change
The most common real-world LAG pattern — calculating the absolute and percentage change from the previous period.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (ORDER BY month_date) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month_date) AS revenue_change,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month_date)) * 100.0
/ LAG(revenue) OVER (ORDER BY month_date),
2
) AS pct_change
FROM monthly_sales
WHERE region = 'North'
ORDER BY month_date;Result:
| region | month_date | revenue | prev_revenue | revenue_change | pct_change |
|---|---|---|---|---|---|
| North | 2024-01-01 | 125000 | NULL | NULL | NULL |
| North | 2024-02-01 | 138000 | 125000 | 13000 | 10.40 |
| North | 2024-03-01 | 142000 | 138000 | 4000 | 2.90 |
| North | 2024-04-01 | 131000 | 142000 | -11000 | -7.75 |
| North | 2024-05-01 | 155000 | 131000 | 24000 | 18.32 |
| North | 2024-06-01 | 162000 | 155000 | 7000 | 4.52 |
April shows a revenue drop of $11,000 (-7.75%) compared to March which is immediately visible from the LAG calculation.
Example 4: PARTITION BY — LAG Within Each Region
Without PARTITION BY, LAG compares rows across all regions which produces incorrect cross-region comparisons. PARTITION BY restarts the LAG window for each region independently.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (
PARTITION BY region
ORDER BY month_date
) AS prev_month_revenue,
revenue - LAG(revenue) OVER (
PARTITION BY region
ORDER BY month_date
) AS mom_change
FROM monthly_sales
ORDER BY region, month_date;Result:
| region | month_date | revenue | prev_month_revenue | mom_change |
|---|---|---|---|---|
| North | 2024-01-01 | 125000 | NULL | NULL |
| North | 2024-02-01 | 138000 | 125000 | 13000 |
| North | 2024-03-01 | 142000 | 138000 | 4000 |
| North | 2024-04-01 | 131000 | 142000 | -11000 |
| North | 2024-05-01 | 155000 | 131000 | 24000 |
| North | 2024-06-01 | 162000 | 155000 | 7000 |
| South | 2024-01-01 | 98000 | NULL | NULL |
| South | 2024-02-01 | 104000 | 98000 | 6000 |
| South | 2024-03-01 | 97000 | 104000 | -7000 |
| South | 2024-04-01 | 112000 | 97000 | 15000 |
| South | 2024-05-01 | 119000 | 112000 | 7000 |
| South | 2024-06-01 | 128000 | 119000 | 9000 |
Each region now resets to NULL at its own January.
Example 5: LAG With Offset — Look Back Multiple Rows
The second argument controls how many rows back to look. Set it to 3 to compare to three months ago.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue, 3) OVER (
PARTITION BY region
ORDER BY month_date
) AS revenue_3_months_ago,
revenue - LAG(revenue, 3) OVER (
PARTITION BY region
ORDER BY month_date
) AS qtr_change
FROM monthly_sales
ORDER BY region, month_date;Result (North region):
| region | month_date | revenue | revenue_3_months_ago | qtr_change |
|---|---|---|---|---|
| North | 2024-01-01 | 125000 | NULL | NULL |
| North | 2024-02-01 | 138000 | NULL | NULL |
| North | 2024-03-01 | 142000 | NULL | NULL |
| North | 2024-04-01 | 131000 | 125000 | 6000 |
| North | 2024-05-01 | 155000 | 138000 | 17000 |
| North | 2024-06-01 | 162000 | 142000 | 20000 |
April through June can now compare to January through March giving you a quarter-over-quarter view within the same query.
Example 6: Using CTE to Clean Up NULL Rows
Wrap the LAG query in a CTE to filter out rows where the previous value is NULL. This is useful for cleaner reporting.
sql
WITH sales_with_lag AS (
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (
PARTITION BY region
ORDER BY month_date
) AS prev_revenue
FROM monthly_sales
)
SELECT
region,
month_date,
revenue,
prev_revenue,
revenue - prev_revenue AS mom_change,
ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2) AS pct_change
FROM sales_with_lag
WHERE prev_revenue IS NOT NULL
ORDER BY region, month_date;The WHERE clause removes the first row of each region where prev_revenue is NULL giving you a clean result with no NULL rows and no division-by-zero risk.
Example 7: Identifying Consecutive Drops
Find months where revenue dropped compared to the previous month. This is useful for flagging concerning trends.
sql
WITH sales_with_lag AS (
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (
PARTITION BY region
ORDER BY month_date
) AS prev_revenue
FROM monthly_sales
)
SELECT
region,
month_date,
revenue,
prev_revenue,
revenue - prev_revenue AS revenue_change,
'Revenue Drop' AS flag
FROM sales_with_lag
WHERE revenue < prev_revenue
ORDER BY region, month_date;Result:
| region | month_date | revenue | prev_revenue | revenue_change | flag |
|---|---|---|---|---|---|
| North | 2024-04-01 | 131000 | 142000 | -11000 | Revenue Drop |
| South | 2024-03-01 | 97000 | 104000 | -7000 | Revenue Drop |
Two months across both regions showed revenue declines. This kind of alert query is extremely valuable in automated monitoring pipelines.
Example 8: LAG on Units Sold — Multiple Columns
LAG can be applied to multiple columns in the same query — each with its own OVER clause.
sql
SELECT
region,
month_date,
revenue,
units_sold,
LAG(revenue) OVER (PARTITION BY region ORDER BY month_date) AS prev_revenue,
LAG(units_sold) OVER (PARTITION BY region ORDER BY month_date) AS prev_units,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month_date) AS rev_change,
units_sold - LAG(units_sold) OVER (PARTITION BY region ORDER BY month_date) AS units_change
FROM monthly_sales
ORDER BY region, month_date;Now you have month-over-month change for both revenue and units sold in a single clean result set.
LAG vs LEAD — What Is the Difference?
LAG and LEAD are mirror functions. LAG looks backward to previous rows. LEAD looks forward to future rows.
sql
SELECT
region,
month_date,
revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month_date) AS prev_month,
LEAD(revenue) OVER (PARTITION BY region ORDER BY month_date) AS next_month
FROM monthly_sales
WHERE region = 'North'
ORDER BY month_date;Result:
| region | month_date | revenue | prev_month | next_month |
|---|---|---|---|---|
| North | 2024-01-01 | 125000 | NULL | 138000 |
| North | 2024-02-01 | 138000 | 125000 | 142000 |
| North | 2024-03-01 | 142000 | 138000 | 131000 |
| North | 2024-04-01 | 131000 | 142000 | 155000 |
| North | 2024-05-01 | 155000 | 131000 | 162000 |
| North | 2024-06-01 | 162000 | 155000 | NULL |
Use LAG when you need to compare to what happened before. Use LEAD when you need to look ahead at what comes next.
LAG vs LEAD
| Feature | LAG | LEAD |
|---|---|---|
| Direction | Looks backward | Looks forward |
| Returns | Previous row value | Next row value |
| First row result | NULL (no previous) | Value of next row |
| Last row result | Value of previous row | NULL (no next) |
| Common use | Month-over-month change | Days until next event |
| Same syntax | Yes | Yes |
Database Compatibility
| Database | LAG Support |
|---|---|
| SQL Server 2012+ | Yes |
| PostgreSQL 8.4+ | Yes |
| MySQL 8.0+ | Yes |
| Oracle 11g+ | Yes |
| SQLite 3.25+ | Yes |
| BigQuery | Yes |
| Snowflake | Yes |
LAG is now supported in all major modern SQL databases.
Real-World Use Cases
Sales and Revenue Analysis — Month-over-month, quarter-over-quarter, and year-over-year growth calculations across product lines, regions, and sales teams.
Financial Reporting — Stock price change from previous close, balance changes between accounting periods, running comparison of actuals vs prior period budget.
User Behavior Analytics — Time between sessions for the same user, identifying churn signals when gaps between logins increase, measuring feature adoption over time.
Operations and Logistics — Comparing delivery times between consecutive shipments, identifying slowdowns in production throughput, monitoring SLA compliance trends.
IoT and Sensor Data — Detecting anomalous readings by comparing each sensor reading to the previous one, identifying equipment degradation patterns.
Common Mistakes to Avoid
- Forgetting ORDER BY — LAG requires ORDER BY inside the OVER clause to define what “previous” means. Without it, the result is undefined and databases will throw an error
- Not using PARTITION BY for grouped data — Without PARTITION BY, LAG compares across all rows regardless of grouping. The last row of Region A bleeds into the first row of Region B — producing incorrect cross-group comparisons
- Arithmetic with NULL results — The first row of each partition returns NULL from LAG. Any arithmetic involving NULL produces NULL. Use COALESCE or the default value argument to handle this:
LAG(revenue, 1, 0) - Using wrong offset — LAG(revenue, 2) looks back two rows — not two months. If your data has gaps (missing months), the offset counts rows, not time periods. Always verify your data has no gaps when using LAG for time-based analysis
- Confusing LAG and LEAD direction — LAG goes backward (previous rows). LEAD goes forward (next rows). Getting this wrong produces results that are the mirror image of what you intended
- Not wrapping in a CTE for further filtering — You cannot filter on a window function result directly in the same WHERE clause. Wrap the LAG query in a CTE or subquery first, then filter on the result
Quick Reference Cheat Sheet
| Task | Query Pattern |
|---|---|
| Previous row value | LAG(col) OVER (ORDER BY date_col) |
| Previous row with default | LAG(col, 1, 0) OVER (ORDER BY date_col) |
| Previous row per group | LAG(col) OVER (PARTITION BY grp ORDER BY date_col) |
| Look back N rows | LAG(col, N) OVER (ORDER BY date_col) |
| MoM change | revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) |
| MoM percent change | (revenue - LAG(revenue)OVER(...)) * 100.0 / LAG(revenue)OVER(...) |
| Filter NULL rows | Wrap in CTE → WHERE prev_col IS NOT NULL |
| Flag drops | WHERE revenue < LAG(revenue) OVER (…) |
The SQL LAG function is one of the most practically useful window functions in data analysis. Once you understand how it works, you will find yourself reaching for it constantly — in sales reporting, financial analysis, operations monitoring, and user behavior analysis.
Here is a quick recap of everything we covered:
- LAG retrieves the value from a previous row without a self-join
- The three arguments are: column, offset (default 1), and default value (default NULL)
- ORDER BY is required — it defines what “previous” means
- PARTITION BY restarts the window for each group — essential for grouped data
- Wrap LAG queries in a CTE to filter or further transform the results
- LEAD is the forward-looking counterpart to LAG
Start with the basic single-partition example, then add PARTITION BY for grouped data, and finally combine with CTE filtering for production-ready analytical queries.
FAQs
What does the SQL LAG function do?
LAG returns the value of a column from a previous row in the result set based on a specified ordering without requiring a self-join. It is a window function used for comparing values across consecutive rows.
What is the difference between LAG and LEAD in SQL?
LAG looks backward and returns a value from a previous row. LEAD looks forward and returns a value from a future row. Both use identical syntax — only the direction differs.
How do I avoid NULL in LAG results?
Use the third argument of LAG to specify a default value: LAG(revenue, 1, 0) returns 0 instead of NULL when no previous row exists. Alternatively, wrap the query in a CTE and filter with WHERE prev_value IS NOT NULL.
Why do I need PARTITION BY in a LAG function?
PARTITION BY restarts the LAG window for each group — like each region or each product. Without it, LAG compares across all rows regardless of group, causing incorrect cross-group comparisons where the last row of one group bleeds into the first row of the next.
Can LAG look back more than one row?
Yes. The second argument controls the offset. LAG(revenue, 3) looks back three rows. This is useful for quarter-over-quarter comparisons when monthly data is stored row by row.
Does LAG work in MySQL?
Yes, but only in MySQL 8.0 and later. Earlier versions of MySQL do not support window functions. All other major databases — PostgreSQL, SQL Server, Oracle, Snowflake, and BigQuery — support LAG as well.