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 one1SELECT2 order_date,3 total, -- LAG(total, 1) reads total from 1 row earlier4 LAG(total, 1) OVER ( -- "Earlier" is defined by order_date5 ORDER BY order_date -- The first row has no previous row, so prev_total is NULL6 ) AS prev_total, -- Change from the previous order; total - NULL gives NULL on the first row7 total - LAG(total, 1) OVER (8 ORDER BY order_date9 ) AS difference10FROM11 orders-- Only customer 1's orders; WHERE runs before LAG12WHERE13 customer_id = 114ORDER BY15 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. 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