MySQL LAG LEAD: Compare a Row with the Previous or Next

LAG(column) returns the value from the previous row of the window. LEAD(column) returns it from the next row. They replace the self joins that were needed before MySQL 8.

query.sql

SQL

CREATE TABLE monthly_sales (month DATE PRIMARY KEY, revenue DECIMAL(10,2));
INSERT INTO monthly_sales VALUES
    ('2026-01-01', 12000), ('2026-02-01', 13500), ('2026-03-01', 11800),
    ('2026-04-01', 15200), ('2026-05-01', 15200), ('2026-06-01', 17900);

SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS previous_month,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_sales
ORDER BY month;

Output

OUTPUT

+------------+----------+----------------+------------+
| month      | revenue  | previous_month | next_month |
+------------+----------+----------------+------------+
| 2026-01-01 | 12000.00 |           NULL |   13500.00 |
| 2026-02-01 | 13500.00 |       12000.00 |   11800.00 |
| 2026-03-01 | 11800.00 |       13500.00 |   15200.00 |
| 2026-04-01 | 15200.00 |       11800.00 |   15200.00 |
| 2026-05-01 | 15200.00 |       15200.00 |   17900.00 |
| 2026-06-01 | 17900.00 |       15200.00 |       NULL |
+------------+----------+----------------+------------+
6 rows in set

The first row has no previous month, so LAG gives NULL. The last row has no next month, so LEAD gives NULL.

Change from the previous row

query.sql

SQL

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change_amount,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS change_percent
FROM monthly_sales
ORDER BY month;

Output

OUTPUT

+------------+----------+---------------+----------------+
| month      | revenue  | change_amount | change_percent |
+------------+----------+---------------+----------------+
| 2026-01-01 | 12000.00 |          NULL |           NULL |
| 2026-02-01 | 13500.00 |       1500.00 |           12.5 |
| 2026-03-01 | 11800.00 |      -1700.00 |          -12.6 |
| 2026-04-01 | 15200.00 |       3400.00 |           28.8 |
| 2026-05-01 | 15200.00 |          0.00 |            0.0 |
| 2026-06-01 | 17900.00 |       2700.00 |           17.8 |
+------------+----------+---------------+----------------+
6 rows in set

Month-over-month growth, in one query. The first month has NULL (nothing to compare).

Arguments: offset and default

LAG(value, offset, default):

offset: how many rows back (default 1).
default: the value to use when there is no such row (default NULL).

query.sql

SQL

SELECT month, revenue,
       LAG(revenue, 3, 0) OVER (ORDER BY month) AS three_months_ago,
       LEAD(revenue, 2)  OVER (ORDER BY month) AS in_two_months
FROM monthly_sales
ORDER BY month;

Output

OUTPUT

+------------+----------+------------------+---------------+
| month      | revenue  | three_months_ago | in_two_months |
+------------+----------+------------------+---------------+
| 2026-01-01 | 12000.00 |             0.00 |      11800.00 |
| 2026-02-01 | 13500.00 |             0.00 |      15200.00 |
| 2026-03-01 | 11800.00 |             0.00 |      15200.00 |
| 2026-04-01 | 15200.00 |         12000.00 |      17900.00 |
| 2026-05-01 | 15200.00 |         13500.00 |          NULL |
| 2026-06-01 | 17900.00 |         11800.00 |          NULL |
+------------+----------+------------------+---------------+
6 rows in set

Per group

Use PARTITION BY so that the comparison stays within a group, for example per product or customer:

query.sql

SQL

CREATE TABLE prices (product VARCHAR(10), valid_from DATE, price INT);
INSERT INTO prices VALUES
    ('pen', '2026-01-01', 10), ('pen', '2026-04-01', 12), ('pen', '2026-07-01', 15),
    ('book', '2026-01-01', 100), ('book', '2026-05-01', 90);

SELECT product, valid_from, price,
       LEAD(valid_from) OVER (PARTITION BY product ORDER BY valid_from) AS valid_until_exclusive,
       price - LAG(price) OVER (PARTITION BY product ORDER BY valid_from) AS price_change
FROM prices
ORDER BY product, valid_from;

Output

OUTPUT

+---------+------------+-------+-----------------------+--------------+
| product | valid_from | price | valid_until_exclusive | price_change |
+---------+------------+-------+-----------------------+--------------+
| book    | 2026-01-01 |   100 | 2026-05-01            |         NULL |
| book    | 2026-05-01 |    90 | NULL                  |          -10 |
| pen     | 2026-01-01 |    10 | 2026-04-01            |         NULL |
| pen     | 2026-04-01 |    12 | 2026-07-01            |            2 |
| pen     | 2026-07-01 |    15 | NULL                  |            3 |
+---------+------------+-------+-----------------------+--------------+
5 rows in set

Each price is valid from its date until the next price starts, which LEAD supplies. This turns a “change log” into validity periods without any self join.

Detect changes and repeated values

query.sql

SQL

1
2
3

SELECT month, revenue
FROM (SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev FROM monthly_sales) AS t
WHERE revenue = prev;

Output

OUTPUT

+------------+----------+
| month      | revenue  |
+------------+----------+
| 2026-05-01 | 15200.00 |
+------------+----------+
1 row in set

April and May both have 15,200: the months where revenue did not change.

Time between events

query.sql

SQL

CREATE TABLE logins (user_name VARCHAR(10), logged_at DATETIME);
INSERT INTO logins VALUES
    ('asha', '2026-07-01 09:00:00'), ('asha', '2026-07-01 09:20:00'), ('asha', '2026-07-03 08:00:00'),
    ('ben',  '2026-07-02 11:00:00'), ('ben',  '2026-07-02 11:05:00');

SELECT user_name, logged_at,
       TIMESTAMPDIFF(MINUTE, LAG(logged_at) OVER (PARTITION BY user_name ORDER BY logged_at), logged_at) AS minutes_since_previous
FROM logins
ORDER BY user_name, logged_at;

Output

OUTPUT

+-----------+---------------------+------------------------+
| user_name | logged_at           | minutes_since_previous |
+-----------+---------------------+------------------------+
| asha      | 2026-07-01 09:00:00 |                   NULL |
| asha      | 2026-07-01 09:20:00 |                     20 |
| asha      | 2026-07-03 08:00:00 |                   2800 |
| ben       | 2026-07-02 11:00:00 |                   NULL |
| ben       | 2026-07-02 11:05:00 |                      5 |
+-----------+---------------------+------------------------+
5 rows in set

A gap above a threshold (say 30 minutes) starts a new “session”. This is the basis of sessionisation (Gaps and Islands).

Remember

LAG/LEAD need an ORDER BY in the window. Without it, “previous” has no meaning.
Add PARTITION BY so the first row of each group does not see the last row of the previous group.
Before MySQL 8.0, you needed JOIN t AS prev ON prev.id = t.id - 1. That breaks as soon as ids have gaps. LAG follows the actual order.
LAG and LEAD are reserved words in MySQL 8.

lightbulb

Wrap to filter

To keep only rows where the change is large, calculate LAG in a derived table or CTE, and filter in the outer query.