SQL LAG() Function: Syntax, Usage, and Examples
The SQL LAG function helps you access data from a previous row in the result set without using a self-join. It’s a key part of window functions and is especially useful when comparing current values to prior ones—such as tracking changes over time.
How to Use the LAG Function in SQL
Use SQL LAG functions inside a SELECT statement along with the OVER() clause. This clause defines the window of rows over which the function operates.
Syntax
sql
LAG(expression, offset, default_value) OVER (
PARTITION BY partition_column
ORDER BY order_column
)
expression: The column or value you want to look back on.offset: How many rows behind to look. Defaults to 1 if omitted.default_value: What to return if no previous row exists (optional).PARTITION BY: (Optional) Splits data into groups, like by customer or region.ORDER BY: Required to define the row order.
Basic Example
sql
SELECT
employee_id,
salary,
LAG(salary) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;
This shows the current salary and the salary of the employee hired just before.
When to Use SQL LAG Functions
SQL LAG functions are ideal when you need to compare each row with a prior one. Use it in scenarios involving time, sequences, or cumulative reporting.
Compare Current and Previous Row Values
You can easily calculate the difference between two rows.
sql
SELECT
sales_date,
revenue,
revenue - LAG(revenue) OVER (ORDER BY sales_date) AS revenue_change
FROM daily_sales;
You’ll see how revenue changed each day.
Track Changes in Status
Use the LAG function in SQL to find when something changed—like a user’s status.
sql
SELECT
user_id,
status,
LAG(status) OVER (PARTITION BY user_id ORDER BY status_date) AS previous_status
FROM user_status_log;
You can compare each user’s current status to the one before.
Calculate Time Differences Between Events
Look at the time gap between events like logins, transactions, or orders.
sql
SELECT
transaction_id,
timestamp,
timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS time_since_last
FROM transactions;
This highlights inactivity or rapid behavior.
Examples of SQL LAG Functions in Action
Here are real examples that show how to apply LAG functions in business and analytics contexts.
Example 1: Monthly Sales Trend
sql
SELECT
month,
SUM(sales) AS total_sales,
LAG(SUM(sales)) OVER (ORDER BY month) AS previous_month_sales
FROM sales_data
GROUP BY month
ORDER BY month;
Compare each month’s total to the previous one.
Example 2: Identify Dropped Revenue
sql
SELECT
customer_id,
billing_month,
billed_amount,
LAG(billed_amount) OVER (PARTITION BY customer_id ORDER BY billing_month) AS last_month_billed,
billed_amount - LAG(billed_amount) OVER (PARTITION BY customer_id ORDER BY billing_month) AS difference
FROM billing;
This detects customers who spent less or stopped paying.
Example 3: Mark First Login After a Gap
sql
SELECT
user_id,
login_time,
CASE
WHEN DATEDIFF(day, LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time), login_time) > 30
THEN 'New Session'
ELSE 'Repeat Login'
END AS session_type
FROM logins;
You can label long gaps between logins as new sessions.
Example 4: Find Previous Orders for a Product
sql
SELECT
product_id,
order_date,
LAG(order_date) OVER (PARTITION BY product_id ORDER BY order_date) AS last_order_date
FROM orders;
Track how often each product gets ordered.
Learn More About SQL LAG Functions
LAG vs LEAD in SQL
Both LAG and LEAD let you peek at neighboring rows, but in opposite directions.
LAG()looks backwardLEAD()looks forward
Example with both:
sql
SELECT
stock_symbol,
trade_date,
closing_price,
LAG(closing_price) OVER (ORDER BY trade_date) AS previous_close,
LEAD(closing_price) OVER (ORDER BY trade_date) AS next_close
FROM stock_prices;
Perfect for building comparisons or trend lines.
Using Default Values
When there’s no previous row—like the first row in a partition—LAG returns NULL. To avoid that, you can supply a default value.
sql
SELECT
user_id,
action_date,
LAG(action_date, 1, '2000-01-01') OVER (PARTITION BY user_id ORDER BY action_date) AS fallback_date
FROM actions;
This returns a fallback date if there’s no earlier record.
SQL Server LAG Function Support
If you’re using SQL Server, you can access the same functionality. Just make sure your version supports window functions (SQL Server 2012 and later).
sql
SELECT
id,
value,
LAG(value) OVER (ORDER BY id) AS previous_value
FROM data_table;
It’s as straightforward as in PostgreSQL, MySQL 8+, or Oracle.
Combine LAG with CTEs
You can wrap your LAG logic in a common table expression (CTE) to simplify complex queries.
sql
WITH ranked_data AS (
SELECT
employee_id,
performance_score,
LAG(performance_score) OVER (PARTITION BY employee_id ORDER BY review_date) AS previous_score
FROM reviews
)
SELECT *,
CASE
WHEN performance_score > previous_score THEN 'Improved'
WHEN performance_score < previous_score THEN 'Declined'
ELSE 'Unchanged'
END AS status_change
FROM ranked_data;
This shows a more readable result, especially in analytics dashboards.
Performance Tips
- Use appropriate indexing on
PARTITION BYandORDER BYcolumns to boost performance. - LAG won’t rearrange your data, so always specify
ORDER BYclearly. - Avoid using LAG in massive subqueries unless absolutely necessary—it can slow things down.