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.

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

Copy
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:

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

By default, offset = 1 and default_value = NULL.

Assume we have the following table:

Table: course_progress

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

Copy
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:

• 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.