LEAD and LAG

LEAD() and LAG() are window functions that let you access values from rows before or after the current one, without using joins.

• LEAD() returns values from following rows.
• LAG() returns values from preceding rows.

These functions are useful for row-to-row comparisons, such as tracking changes in progress or examining adjacent data points.

Syntax

The basic syntax for LEAD() and LAG() is as follows:

LEAD and LAG syntax

SELECT column,
  LAG(column) OVER (ORDER BY ...) AS previous_value,
  LEAD(column) OVER (ORDER BY ...) AS next_value
FROM table;

You can also provide default values and custom offsets:

LAG syntax with default values and custom offsets

LAG(column, offset, default_value) OVER (...)

By default, offset = 1 and default_value = NULL.

Example: CodeFriends Progress

Assume we have the following table:

Table: course_progress

user_id	log_date	course_name	progress_percent
1	2024-06-01	SQL Basics	40
1	2024-06-02	SQL Basics	60
1	2024-06-03	SQL Basics	80
1	2024-06-04	SQL Basics	100

We want to compare each day’s progress to the previous and next day for every user.

LEAD and LAG example

SELECT user_id,
  log_date,
  course_name,
  progress_percent,
  LAG(progress_percent) OVER (PARTITION BY user_id ORDER BY log_date) AS previous_progress,
  LEAD(progress_percent) OVER (PARTITION BY user_id ORDER BY log_date) AS next_progress
FROM course_progress;

The query returns the following:

Result:

user_id	log_date	course_name	progress_percent	previous_progress	next_progress
1	2024-06-01	SQL Basics	40	NULL	60
1	2024-06-02	SQL Basics	60	40	80
1	2024-06-03	SQL Basics	80	60	100
1	2024-06-04	SQL Basics	100	80	NULL

When are LEAD and LAG useful?

• Use LAG() to compare a row with earlier values (e.g., progress since the previous day).
• Use LEAD() to compare a row with upcoming values (e.g., forecast next step or change).

In practice, they’re often used for time-series analysis, trend tracking, and change detection.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In