LAG and LEAD in SQL: Compare to Last Month
LAG reads a value from the row before, and LEAD reads the row after. Build monthly sales, put last month next to this month, and work out the month-on-month change, on a sample online shop database.
Step by step
Month on month
- Up or down?. Did sales go up or down since last month? In SQL, LAG reads a value from the row before and LEAD reads a value from the row after. That lets you put each month next to the one before or after it.
- The orders table. All the data comes from the orders table. Each order has a date (order_date) and a total (order_total).
Monthly sales
- Sales per month. First, build sales per month. strftime('%m', order_date) keeps only the month number from the date. GROUP BY month then adds up each month's orders with SUM(order_total). Run it and you get 12 rows, one for each month, starting with January.
SELECT strftime('%m', order_date) AS month, SUM(order_total) AS sales FROM orders GROUP BY month;
LAG
- LAG. Now add LAG(SUM(order_total)) OVER (ORDER BY strftime('%m', order_date)) and call it last_month. The window can't use the new name month here, so it repeats the month formula. Run it. Each row now shows the month before it. January's last_month is empty (NULL), because the data has no month before January.
AS sales, LAG(SUM(order_total)) OVER (ORDER BY strftime('%m', order_date)) AS last_month
The change
- The change. The change is the real answer. Replace the sales and last_month columns with SUM(order_total) minus the same LAG, and call it change. Run it. February fell by $437.50 and April grew by $242.50. A minus sign means sales went down. January's change is empty, because there is nothing to subtract.
SUM(order_total) - LAG(SUM(order_total)) OVER (ORDER BY strftime('%m', order_date)) AS change
LEAD
- LEAD. LEAD works the same way but looks forward. Bring back the sales column and add LEAD(SUM(order_total)) with the same OVER clause. Call it next_month. Run it. Each month now shows the month after it, so January's next_month is February's $1,440. December's next_month is empty, because nothing comes after it.
SUM(order_total) AS sales, LEAD(SUM(order_total)) OVER (ORDER BY strftime('%m', order_date)) AS next_month
Recap
- LAG and LEAD. LAG reads the row before and LEAD reads the row after. The ORDER BY inside OVER decides what "before" means. Subtract LAG from this month's sales to get the change.
Common mistakes
- Grouping by month only when the data covers several years. strftime('%m', order_date) puts January 2025 and January 2026 into the same row. Use strftime('%Y-%m', order_date) for the group and in the ORDER BY so each year's months stay separate and in the right order.
- Months with no orders. If March has no orders, it has no row, so April's LAG compares April to February. Check that you have one row per month before trusting the change, or join to a full list of months.
- Leaving out or changing the ORDER BY inside OVER. The ORDER BY decides which row counts as "before". Some databases, such as SQL Server, won't run LAG without it, and a different order gives a different answer. Order by the same month value you group by.
- Turning the empty first month into 0. Giving LAG a default of 0 makes January's change equal all of January's sales, which looks like huge growth. Leave it empty (NULL) unless you really had $0 of sales the month before.
When a business analyst uses this
In a benefits review after a new checkout process goes live, a BA groups orders by month and uses SUM(order_total) - LAG(SUM(order_total)) to show each month's change. The steering committee can then see whether sales actually rose after go-live, rather than comparing two totals by eye.
Questions people ask
What is the difference between LAG and LEAD in SQL?
LAG returns a value from an earlier row and LEAD returns a value from a later row, in the order set by ORDER BY inside OVER. By default both look 1 row away and return NULL when that row doesn't exist.
What is the syntax of LAG and LEAD in SQL?
LAG(value, offset, default) OVER (PARTITION BY ... ORDER BY ...), and LEAD is written the same way. The offset defaults to 1 and the default to NULL. PARTITION BY starts again for each group, such as each region.
Do LAG and LEAD work in SQL Server?
Yes, SQL Server has had LAG and LEAD since SQL Server 2012, and it needs ORDER BY inside OVER. strftime is a SQLite function, so in SQL Server use MONTH(order_date) to get the month.
