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? Every manager asks that. In SQL, lag looks at the row before, and lead looks at the row after. Let's compare months.
- The orders table. Everything comes from the orders table. Each order has a date and a total.
Monthly sales
- Sales per month. First, sales per month. Strf time with percent m keeps only the month from the date. Group by month adds up each month's orders. Twelve rows, one for each month of the year, January first.
SELECT strftime('%m', order_date) AS month, SUM(order_total) AS sales FROM orders GROUP BY month;
LAG
- LAG. Now add lag of the sales, over, order by month. The window can't see the new name month yet, so we repeat the month formula. Call it last month. Each month now shows the month before it. January's last month is empty, because there's no month before January in our data.
AS sales, LAG(SUM(order_total)) OVER (ORDER BY strftime('%m', order_date)) AS last_month
The change
- The change. Last month on its own is nice, but the change is the answer. Swap both columns for this month's sales minus lag, and call it change. February fell by four hundred and thirty seven fifty. April grew by two hundred and forty two fifty. A minus sign means sales went down.
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 the sales back, and add lead of the sales. Call it next month. Now each month shows the month after it. January shows February's one thousand four hundred and forty. At the bottom, December 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. So, lag reads the row before, and lead reads the row after. Over, order by, decides what before means. Subtract lag from this month to get the change.
