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

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

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.

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.

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.

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

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

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

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

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 backward
• LEAD() looks forward

Example with both:

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.

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).

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.

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 BY and ORDER BY columns to boost performance.
• LAG won’t rearrange your data, so always specify ORDER BY clearly.
• Avoid using LAG in massive subqueries unless absolutely necessary—it can slow things down.