lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

lesson12Title

sqlIntroAdvancedChapter3Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

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;

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

<br/>

## Syntax

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

```sql title="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:

```sql title="LAG syntax with default values and custom offsets"
LAG(column, offset, default_value) OVER (...)
```

<br/>

## 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               |

<br/>

We want to see the progress of each user over time, and compare it to the previous and next day's progress.

```sql title="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           |

<br/>

You can use `LEAD()` and `LAG()` to compare the progress of each user over time.

The PARTITION BY clause is used to divide rows into groups without collapsing them, allowing for window function calculations to be applied independently within each group. This is different from GROUP BY, which aggregates rows into a single summary row.

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

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

LEAD and LAG

Syntax

Example: CodeFriends Progress

What is the primary purpose of the PARTITION BY clause in SQL window functions?