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's
1SELECT
2 order_date,
3 total,
-- LAG(column, offset, default): total from 1 row back, or 0 if there is none
4 LAG(total, 1, 0) OVER (
-- "Previous" is defined by date order
5 ORDER BY order_date
6 ) AS prev_total,
-- Day-over-day change; on the first row it equals total, because the default is 0
7 total - LAG(total, 1, 0) OVER (
-- Same window order as above
8 ORDER BY order_date
9 ) AS diff
-- Reads from the daily_sales table
10FROM
11 daily_sales
-- Shows the days in order
12ORDER BY
13 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

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

More SQL examples at level 5

← SQL typing practice