LEAD and LAG
LEAD() and LAG() are window functions that allow you to look at other rows relative to the current row's position, without needing joins.
LEAD()looks forward to the next row.LAG()looks backward to the previous row.
These are useful for comparisons across rows, such as tracking changes in user progress or viewing 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 (...)
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 see the progress of each user over time, and compare it to the previous and next day's progress.
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 |
You can use LEAD() and LAG() to compare the progress of each user over time.
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.