LAG Window Function in SQL

Level 5203 keystrokes

LAG accesses a row at a specified offset before the current row within the partition.

SQL code

-- Compare each of customer 1's orders with the previous one
1SELECT
2 order_date,
3 total,
-- LAG(total, 1) reads total from 1 row earlier
4 LAG(total, 1) OVER (
-- "Earlier" is defined by order_date
5 ORDER BY order_date
-- The first row has no previous row, so prev_total is NULL
6 ) AS prev_total,
-- Change from the previous order; total - NULL gives NULL on the first row
7 total - LAG(total, 1) OVER (
8 ORDER BY order_date
9 ) AS difference
10FROM
11 orders
-- Only customer 1's orders; WHERE runs before LAG
12WHERE
13 customer_id = 1
14ORDER BY
15 order_date;

Output

+------------+--------+------------+------------+
| order_date | total  | prev_total | difference |
+------------+--------+------------+------------+
| 2024-01-10 | 150.00 |       NULL |       NULL |
| 2024-02-15 | 200.00 |     150.00 |      50.00 |
| 2024-03-20 | 175.00 |     200.00 |     -25.00 |
+------------+--------+------------+------------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 6
  • ,× 5
  • (× 4
  • )× 4
  • -× 1
  • ;× 1
  • =× 1

More SQL examples at level 5

← SQL typing practice