How do I compare each row with the previous month in SQL with LAG?
Pull the previous month's sales onto each row with LAG and calculate how much they went up or down.
Free course. Your Maestro, an AI, .
"How much more did we sell than last month?" To subtract, you need last month's sales on the same row as this month's. That's exactly what LAG does: it pulls a value from the previous row in its partition.
LAG(ventas)getsventasfrom the previous row.LEAD(ventas)getsventasfrom the next row.OVER (PARTITION BY sucursal ORDER BY mes)defines "previous": the prior month in the same branch.
By default, LAG looks one row back and returns NULL when there is no previous row.
ventas - LAG(ventas) OVER (
PARTITION BY sucursal
ORDER BY mes
) AS cambioTry it
Before you write the query, answer the question below. Then run it and check your answer on the January row. If one branch's numbers get mixed up with another's, ask your Maestro.
Check yourself: Before you run it: what shows up in the cambio column on Centro's January row?
Return sucursal, mes, ventas, ventasanterior (sales from the previous row in the same branch, using LAG) and cambio (ventas minus ventasanterior). Sort by sucursal, mes. You're done when you see 35 rows and Centro's February says 165100 and -9400.
Your turn
Return sucursal, mes, ventas, ventas_anterior (sales from the previous row in the same branch, using LAG) and cambio (ventas minus ventas_anterior). Sort by sucursal, mes. You're done when you see 35 rows and Centro's February says 165100 and -9400.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Row by row
Here are Centro's first few months from the solution in Maestro's sandbox. Read the table diagonally: each row's ventas drops down into the next row's ventas_anterior.
| mes | ventas | ventas_anterior | cambio |
|---|---|---|---|
| 2025-01-01 | 165100 | NULL | NULL |
| 2025-02-01 | 155700 | 165100 | -9400 |
| 2025-03-01 | 178700 | 155700 | 23000 |
| 2025-04-01 | 175800 | 178700 | -2900 |
LAG doesn't look up "last month" on a calendar. It does something simpler: inside Centro's partition, sorted by mes, it takes the row right above. When the query moves on to Norte's first row, there's no row above it within Norte, so Norte's January gets NULL too.
The chart plots Centro's cambio column. Vega-Lite has its own lag window, and the chart uses it to compute each bar from the sales.
January gets NULL in both new columns: it's its branch's first row, so there's no previous row. And any subtraction involving NULL gives NULL. That's the right answer: you don't know how much January changed, because the table has no December 2024.
Thanks to PARTITION BY sucursal, Norte's January isn't compared with Centro's December. Without it, the three branches would run together as one long list.
What changes the number
The innocent-looking 0. LAG takes a third argument: the value to return when there's no previous row. It's tempting to write LAG(ventas, 1, 0) to get rid of the NULL. In the sandbox, that version reported a change of 165100 for Centro's January, as if the branch grew by everything it sold. A NULL says "I don't know"; a made-up 0 says something false. Keep the NULL, or explain it in the report.
The percentage. You'll rarely be asked for the difference; you'll be asked "how much did it grow, in percent": the change divided by the previous sales. In the run, Centro's February came out at -5.7 and March at 14.8. When the same window shows up three times, the WINDOW clause lets you name it once, and DuckDB accepted it as written (below). If the previous value can be 0, guard the division with NULLIF(LAG(ventas) OVER w, 0).
A tie in the sort order. If two rows in the partition share the same ORDER BY value, which one counts as "previous" isn't guaranteed, just like with ROW_NUMBER. Break the tie with a column that doesn't repeat.
Looking ahead. LEAD works the same way in the other direction: in the run, LEAD(ventas) on Centro's January returned 155700, February's sales, and on December it gave NULL because there's no next month.
SELECT sucursal, mes, ventas,
ventas - LAG(ventas) OVER w AS cambio,
ROUND(100.0 * (ventas - LAG(ventas) OVER w)
/ LAG(ventas) OVER w, 1) AS pct_cambio
FROM ventas_mensuales
WINDOW w AS (PARTITION BY sucursal ORDER BY mes)
ORDER BY sucursal, mes;If it comes up in an interview, it may sound like "calculate month-over-month growth for each product," "how many days passed between each of a user's purchases and the one before?" or "find users who bought on two consecutive days." All three are LAG with the right partition. The days one is the same idea with dates: you subtract the previous row's date. In the pedidos table, Elena has two orders on February 6, so the second one came out at 0 days, and her order 1028 came 15 days after the one before it. If you want to practice one of these out loud, ask your Maestro to quiz you.
In your own reports: find one that compares "against the previous period" and check how it's calculated. If someone copies last month's column by hand today, or joins the table to itself, LAG does it in one line. And before you hand it off, read the next page: a missing month changes what "previous" means.
SELECT cliente, pedido_id, fecha,
fecha - LAG(fecha) OVER (
PARTITION BY cliente
ORDER BY fecha, pedido_id
) AS dias_desde_el_anterior
FROM pedidos
ORDER BY cliente, fecha, pedido_id;