LAG()

Access the previous row's value — essential for trend analysis

Advanced 12 minLAGwindowprevious

Looking at the Previous Row

LAG() retrieves a value from a previous row in the window. It's the essential tool for comparing each row to what came before it in a sequence. Here's how to use it:

Three arguments — The column to retrieve, the offset (default 1 = one row back), and a default value (default NULL) to use when there's no predecessor available for the current row.

The essential pattern — total - LAG(total) OVER (ORDER BY date) gives you the row-over-row change for each record. This is fundamental for trend detection, growth analysis, and spotting anomalies in sequential data.

First row returns NULL — The first row in each partition has no predecessor, so LAG returns NULL (or your specified default value). Plan for this in your queries by using COALESCE or CASE WHEN.

Works with PARTITION BY — LAG(total) OVER (PARTITION BY customer_id ORDER BY date) looks back within each customer's orders separately, so customer boundaries never cross over.

Knowledge Canvas

How LAG Works

Access the previous row's value

LAG(col, n, default) looks back n rows (default: 1)
Returns NULL if no previous row exists (unless default set)
ORDER BY inside OVER() defines "previous"
PARTITION BY creates independent sequences

LAG Patterns

Time-series analysis essentials

Period-over-period change: value - LAG(value)
Growth rate: (value - LAG(value)) * 100.0 / LAG(value)
Gap detection: date - LAG(date) shows intervals
Session boundaries: flag when gap exceeds threshold

NULL Handling

Edge cases with LAG

First row in partition → LAG returns NULL
Provide default: LAG(val, 1, 0) returns 0 instead
LAG over NULL values → returns the NULL from previous row
COALESCE(LAG(val), 0) for safe calculations

When to Use LAG

Classic time-series scenarios

Month-over-month revenue comparison
Day-over-day user growth
Detecting streaks and breaks in sequences
Calculating moving differences and velocities

Syntax

Syntax Template

1-- Previous row value (1 row back by default)

2LAG(column) OVER (

3 ORDER BY col

6-- Value from N rows back

7LAG(column, 2) OVER (

8 ORDER BY col

11-- Default value when there is no previous row

12LAG(column, 1, 0) OVER (

13 ORDER BY col

14)

LAG(col)Previous row value (1 row back by default)

LAG(col, n)Value from n rows back

LAG(col, 1, default)Replace NULL (no previous row) with default

Sample Data

orders (customer 1)

2 rows

order_date	total
2024-01-15	59.98
2024-02-20	12.5

Worked Example

Show customer C1001's orders with the previous order total and the change.

SQL

1SELECT

2 order_date,

3 total,

4 LAG(total) OVER (ORDER BY order_date) AS prev_total,

5 total - LAG(total) OVER (ORDER BY order_date) AS change

6FROM orders

7WHERE customer_id = 'C1001'

8ORDER BY

9 order_date;

The first row has no predecessor, so prev_total is NULL and change is NULL. For the second order (12.50), change = total - LAG(total) = 12.50 - 59.98 = -47.48 (a decrease of 47.48). For the third (89.99), change = 89.99 - 12.50 = 77.49 (an increase).

Output

2 rows

order_date	total	prev_total	change
2024-01-15	59.98	NULL	NULL
2024-02-20	12.5	59.98	-47.48

Key Concepts

1LAG(col) = previous row value, LAG(col, 2) = 2 rows back

2First row in each partition returns NULL (no predecessor)

3Pattern: value - LAG(value) = row-over-row change

4Provide a default: LAG(col, 1, 0) to avoid NULLs

Pro Tip

Comparing a value to its predecessor — last month's revenue, yesterday's price, the previous quarter — is fundamental to time-series analysis.

When to Use

Month-over-month growth, day-over-day changes, identifying spikes or drops, comparing sequential measurements.

Challenge

Solve the problem below

For all orders (sorted by date), show order_date, total, the previous order total (prev_total), and whether the total increased or decreased (label it trend: "Up", "Down", or "First" for the first row). Show top 8 rows.

Your Query

DENSE_RANK()

LEAD()