LAG Function in SQL
Level 5186 keystrokes
LAG reads the total of the previous day without a self-join, with 0 as the default for the first row; the query shows each day's sales, the previous day's sales and the change (1000, 500, -300).
SQL code
-- Shows each day's sales next to the previous day's1SELECT2 order_date,3 total, -- LAG(column, offset, default): total from 1 row back, or 0 if there is none4 LAG(total, 1, 0) OVER ( -- "Previous" is defined by date order5 ORDER BY order_date6 ) AS prev_total, -- Day-over-day change; on the first row it equals total, because the default is 07 total - LAG(total, 1, 0) OVER ( -- Same window order as above8 ORDER BY order_date9 ) AS diff-- Reads from the daily_sales table10FROM11 daily_sales-- Shows the days in order12ORDER BY13 order_date;Output
+------------+-------+------------+------+ | order_date | total | prev_total | diff | +------------+-------+------------+------+ | 2024-01-01 | 1000 | 0 | 1000 | | 2024-01-02 | 1500 | 1000 | 500 | | 2024-01-03 | 1200 | 1500 | -300 | +------------+-------+------------+------+ 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.
,× 7_× 6(× 4)× 4-× 1;× 1