by
SQL Tutorial › Window Functions › LAG & LEAD

SQL LAG and LEAD Functions

AdvancedLesson 47 of 6910 min read6 queries run on MySQL 8.4

LAG reads a value from the previous row and LEAD from the next row, in an order you choose, without a self join. They answer “how much did revenue change since last month?”, “how many days passed between a customer’s orders?” and “who was hired right after this person?”.

Advertisement
In this lesson
  • Read the previous row with LAG and the next with LEAD
  • Calculate changes and growth between rows
  • Work per group with PARTITION BY
  • Use offsets, defaults, FIRST_VALUE and LAST_VALUE

Syntax

MySQL
LAG(expression [, offset [, default]])  OVER ([PARTITION BY ...] ORDER BY ...)LEAD(expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)
  • offset: how many rows back or forward (default 1).
  • default: the value when there is no such row (default NULL).

Sample data

orders · 10 rows
order_idcustomer_idorder_datestatus
100112025-01-05Delivered
100222025-01-17Delivered
100312025-02-02Delivered
100432025-02-14Cancelled
100542025-03-08Delivered
100652025-03-22Shipped
100722025-04-10Delivered
100812025-04-28Pending
100932025-05-15Delivered
101052025-06-01Delivered

Previous and next order

SQL
SELECT order_id, order_date,       LAG(order_date)  OVER (ORDER BY order_date) AS previous_order,       LEAD(order_date) OVER (ORDER BY order_date) AS next_orderFROM ordersORDER BY order_date;
ResultMySQL 8.4
order_idorder_dateprevious_ordernext_order
10012025-01-05NULL2025-01-17
10022025-01-172025-01-052025-02-02
10032025-02-022025-01-172025-02-14
10042025-02-142025-02-022025-03-08
10052025-03-082025-02-142025-03-22
10062025-03-222025-03-082025-04-10
10072025-04-102025-03-222025-04-28
10082025-04-282025-04-102025-05-15
10092025-05-152025-04-282025-06-01
10102025-06-012025-05-15NULL
10 rows in set
How this query works, step by step
  1. OVER (ORDER BY order_date) puts the orders in date order.
  2. LAG(order_date) takes the date from the row just before; the first row has none, so it is NULL.
  3. LEAD(order_date) takes it from the row just after; the last row gets NULL.

Days between orders

MySQL
SELECT order_id, order_date,       DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY order_date)) AS days_since_previousFROM ordersORDER BY order_date;
ResultMySQL 8.4
order_idorder_datedays_since_previous
10012025-01-05NULL
10022025-01-1712
10032025-02-0216
10042025-02-1412
10052025-03-0822
10062025-03-2214
10072025-04-1019
10082025-04-2818
10092025-05-1517
10102025-06-0117
10 rows in set

Per customer: PARTITION BY

Days between each customer’s own orders:

MySQL
SELECT customer_id, order_id, order_date,       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,       DATEDIFF(order_date,                LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_gapFROM ordersORDER BY customer_id, order_date;
ResultMySQL 8.4
customer_idorder_idorder_dateprev_orderdays_gap
110012025-01-05NULLNULL
110032025-02-022025-01-0528
110082025-04-282025-02-0285
210022025-01-17NULLNULL
210072025-04-102025-01-1783
310042025-02-14NULLNULL
310092025-05-152025-02-1490
410052025-03-08NULLNULL
510062025-03-22NULLNULL
510102025-06-012025-03-2271
10 rows in set
How this query works, step by step
  1. PARTITION BY customer_id makes each customer’s orders a separate sequence.
  2. The first order of every customer has no previous order: NULL.
  3. Customer 1 waited 28 days between their first two orders, then 85 days.

Month-over-month revenue change

First total revenue per month in a CTE, then compare each month with the previous one:

MySQL
WITH monthly AS (  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,         SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY month)SELECT month, revenue,       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,       revenue - LAG(revenue) OVER (ORDER BY month) AS change_amt,       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))             / LAG(revenue) OVER (ORDER BY month), 1) AS change_pctFROM monthlyORDER BY month;
ResultMySQL 8.4
monthrevenueprev_revenuechange_amtchange_pct
2025-0113743NULLNULLNULL
2025-02429813743-9445-68.7
2025-03878342984485104.4
2025-0421795878313012148.1
2025-05349921795-18296-83.9
2025-0611453499-2354-67.3
6 rows in set
How this query works, step by step
  1. The CTE gives one row per month with its revenue.
  2. LAG(revenue) brings the previous month’s revenue onto each row.
  3. The difference and the percentage are ordinary arithmetic on those two columns.
  4. January has no previous month, so its change is NULL.

Offsets and defaults

SQL
SELECT first_name, hire_date,       LAG(first_name, 2, '-')  OVER (ORDER BY hire_date) AS hired_two_before,       LEAD(first_name, 1, '-') OVER (ORDER BY hire_date) AS hired_nextFROM employeesORDER BY hire_date;
ResultMySQL 8.4
first_namehire_datehired_two_beforehired_next
Meera2015-01-05–Vikram
Vikram2017-11-20–Asha
Asha2018-04-12MeeraSneha
Sneha2019-02-25VikramRahul
Rahul2019-07-01AshaKavya
Kavya2020-09-09SnehaPriya
Priya2021-01-18RahulNeha
Neha2022-03-14KavyaKaran
Karan2022-08-29PriyaArjun
Arjun2023-06-05NehaRohan
Rohan2024-01-22KaranAmit
Amit2025-06-02Arjun–
12 rows in set
How this query works, step by step
  1. LAG(first_name, 2, '-') looks two rows back; for the first two rows there is no such row, so the default '-' is shown.
  2. LEAD(first_name, 1, '-') looks one row forward, with '-' for the last row.

FIRST_VALUE and LAST_VALUE

The first hire of each department next to every employee:

SQL
SELECT dept_id, first_name, hire_date,       FIRST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY hire_date) AS first_hire,       LAST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY hire_date                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_hireFROM employeesWHERE dept_id IN (1, 2)ORDER BY dept_id, hire_date;
ResultMySQL 8.4
dept_idfirst_namehire_datefirst_hirelatest_hire
1Asha2018-04-12AshaAmit
1Rahul2019-07-01AshaAmit
1Priya2021-01-18AshaAmit
1Karan2022-08-29AshaAmit
1Amit2025-06-02AshaAmit
2Vikram2017-11-20VikramArjun
2Neha2022-03-14VikramArjun
2Arjun2023-06-05VikramArjun
8 rows in set
Watch out

With ORDER BY in the window, the default frame ends at the current row, so a plain LAST_VALUE(...) just returns the current row’s value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the whole partition.

Exercise: Salary step to the next person

Order employees by salary (lowest first) and show, for each, the next higher salary and the difference.

Expected resultMySQL 8.4
first_namesalarynext_salarystep
Amit250004500020000
Rohan45000480003000
Arjun48000540006000
Neha540007200018000
Priya72000720000
Karan72000780006000
Sneha780008800010000
Kavya880009800010000
Rahul980001050007000
Vikram10500012500020000
Asha125000250000125000
Meera250000NULLNULL
12 rows in set
Hint 1

LEAD(salary) OVER (ORDER BY salary).

Quick quiz

1. What does LAG(x) return for the first row of a partition?

2. Which function reads the next row?

3. Why does LAST_VALUE often return the current row’s value?

LAG and LEAD FAQ

What does the LAG function do in SQL?

LAG(column) returns the value of the column from the previous row in the window’s order, letting you compare a row with the one before it.

How do I calculate month-over-month growth in SQL?

Aggregate by month, then use LAG(revenue) OVER (ORDER BY month) to get the previous month and compute (revenue - prev) / prev * 100.

How do I replace NULL in LAG?

Pass a default as the third argument: LAG(revenue, 1, 0) OVER (...).