How do I compare each row with the previous month in SQL with LAG?

"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) gets ventas from the previous row.
• LEAD(ventas) gets ventas from the next row.
• OVER (PARTITION BY sucursal ORDER BY mes) defines "previous": the previous row in the same branch, sorted by month; it matches the previous calendar month only when there are no gaps.

By default, LAG looks one row back and returns NULL when there is no previous row.

Try 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.

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.

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.

PARTITION BY sucursal keeps branches separate. Without it, with ORDER BY mes, a row may take another branch's row from the same month as its predecessor, and tied months have no unique order. The final ORDER BY sucursal, mes only sorts what you see; it doesn't change the window's order.

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.

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.