SQL LAG

What is SQL LAG and how is it used?

Returns data from a previous row within the same result set without the need for a self-join.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LAG: PostgreSQL, MySQL 8.0+, SQL Server 2012+, Oracle analytic functions, Snowflake, BigQuery, Redshift, SQLite 3.25+

SQL LAG Full Explanation

LAG is an analytic window function that lets you look backward in a result set and retrieve the value of a column from a preceding row determined by the current ORDER BY clause. It is evaluated after the WHERE, GROUP BY, and HAVING phases but before the final SELECT output, making it ideal for calculating differences, running comparisons, or detecting changes between consecutive rows. Because LAG operates on a logical window, it does not alter the underlying table and can be combined with PARTITION BY to restart the backward look for each subgroup. If the requested preceding row falls outside the partition, the function returns NULL or the user-supplied default. Offsets start at 1, meaning LAG(col) is equivalent to LAG(col, 1).

SQL LAG Syntax

LAG ( value_expression [, offset [, default ] ] )
OVER ( [ PARTITION BY partition_expression ]
       ORDER BY sort_expression [ ASC | DESC ] )

SQL LAG Parameters

- value_expression (Any) - Column or expression to return from the previous row.
- offset (Integer, optional) - Number of rows back to look. Default is 1.
- default (Same type as value_expression, optional) - Value to return when the offset row does not exist. Default is NULL.
- PARTITION BY (Expression list, optional) - Defines subsets where the offset calculation restarts.
- ORDER BY (Expression list, required) - Establishes the row order used for offset calculation.

Example Queries Using SQL LAG

-- 1. Basic previous order total per customer
SELECT customer_id,
       order_date,
       total_amount,
       LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_total
FROM   orders;

-- 2. Compare with the order two rows before
SELECT order_id,
       total_amount,
       LAG(total_amount, 2) OVER (ORDER BY order_id) AS total_two_orders_ago
FROM   orders;

-- 3. Provide a default when no prior row exists
SELECT order_date,
       total_amount,
       LAG(total_amount, 1, 0) OVER (ORDER BY order_date) AS prev_total_or_zero
FROM   orders;

Expected Output Using SQL LAG

Each query appends an additional column containing the requested previous-row value
Where the offset row is unavailable, NULL (or the specified default) is returned
No data is modified; results are read-only

Use Cases with SQL LAG

Calculate day-over-day, week-over-week, or month-over-month changes
Identify first occurrences or trend reversals by comparing current and prior values
Compute churn indicators such as a user’s last activity date versus current date
Build running difference metrics without self-joins, improving readability and performance

Common Mistakes with SQL LAG

Forgetting ORDER BY inside the OVER clause, resulting in nondeterministic output
Assuming LAG reorders rows; it only references them
Using GROUP BY when window functions already provide the needed aggregation
Expecting LAG to skip NULLs automatically; it does not unless filtered explicitly

First Introduced In

SQL:1999

Frequently Asked Questions

What does SQL LAG do?

LAG fetches the value of a column from a previous row in the result set so you can compare current and prior values without self-joins.

How do I set the number of rows to look back?

Pass an integer offset as the second argument: LAG(column, 2) looks two rows earlier. If omitted, the default offset is 1.

What happens when there is no preceding row?

LAG returns NULL unless you provide a third argument as a default value. Example: LAG(total, 1, 0) returns 0 instead of NULL.

Does LAG work in all databases?

Most modern databases support it, including PostgreSQL, SQL Server, Oracle, MySQL 8.0+, SQLite 3.25+, BigQuery, and Snowflake. Always verify your specific version.