Why does LAG skip a missing month, and how do I find it?
See that LAG returns the previous row, not the previous month, and find the gap in Sur's data.
Free course. Your Maestro, an AI, .
On the previous page, Sur's cambio for July was -2000. That looks like a small dip from June. But the table has no June for Sur: it has 11 rows, not 12. So what was July compared against?
May. LAG returns the previous row in the partition, and it knows nothing about calendars. If a row is missing, it compares with whichever row is there. Nothing in the result warns you: the -2000 looks just as trustworthy as the rest.
Warning: LAG counts rows, not months
A missing month doesn't show up as NULL: there's simply no row. Before you report a "change from last month," check that the previous row really is last month.
Row by row
Here are Sur's rows from April through August in the previous page's query, as the sandbox returned them:
| mes | ventas | ventas_anterior | cambio |
|---|---|---|---|
| 2025-04-01 | 121000 | 112700 | 8300 |
| 2025-05-01 | 119600 | 121000 | -1400 |
| 2025-07-01 | 117600 | 119600 | -2000 |
| 2025-08-01 | 122900 | 117600 | 5300 |
Look at the mes column: it jumps from May to July. Sur's partition has 11 rows, and as far as LAG is concerned the July row sits right under May's, so May is "the previous one." The query didn't make a mistake: it did exactly what you asked. The real question is a different one.
Orders placed more than 14 days after the previous one
Worked example · 3 steps
In the pedidos table, we want the orders placed more than 14 days after the same customer's previous order, with both dates and the gap in days. It's the same problem you'll solve with months, just with days.
Work out the next step yourself, then open it to compare.
With months, the check works the same way: also get the month of the previous row and measure the gap. In DuckDB, date_diff('month', start, end) counts the months between two dates: between May and July it gives 2.
Try it
Find every row where the previous row in its branch is not the previous month. You need two steps, as in the example: calculate LAG(mes), then filter. First, answer the question below. If you're not sure how to split it into two steps, ask your Maestro.
Check yourself: Before you run it: how many rows will your gap query return from the 35 rows in ventas_mensuales?
Return sucursal, mes, mesanterior (the month on the previous row of its branch, using LAG(mes)) and mesesdedistancia (using datediff('month', mesanterior, mes)), only for rows where the gap is greater than 1. Sort by sucursal, mes. You're done when you've found Sur's gap.
Your turn
Return sucursal, mes, mes_anterior (the month on the previous row of its branch, using LAG(mes)) and meses_de_distancia (using date_diff('month', mes_anterior, mes)), only for rows where the gap is greater than 1. Sort by sucursal, mes. You're done when you've found Sur's gap.
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.
Just one row: Sur's July, whose previous row is May, 2 months back. And notice January: its mes_anterior is NULL, and date_diff with a NULL gives NULL, which isn't greater than 1. That's why January doesn't show up.
With QUALIFY you can skip the WITH and use the SELECT alias directly. In the sandbox, this version returned the same row as the solution:
SELECT sucursal, mes,
LAG(mes) OVER (PARTITION BY sucursal ORDER BY mes) AS mes_anterior,
date_diff('month', mes_anterior, mes) AS meses_de_distancia
FROM ventas_mensuales
QUALIFY meses_de_distancia > 1
ORDER BY sucursal, mes;Filling in the calendar
Finding the gap is half the job. If your report really needs "vs. last month," the fix is to give LAG one row for every month, whether it has data or not. First you build the full list of months with generate_series, then you LEFT JOIN your sales to it: a month with no sales keeps its row, with ventas as NULL.
WITH calendario AS (
SELECT CAST(m AS DATE) AS mes
FROM generate_series(DATE '2025-01-01', DATE '2025-12-01',
INTERVAL 1 MONTH) AS t(m)
),
sur AS (
SELECT c.mes, v.ventas
FROM calendario c
LEFT JOIN ventas_mensuales v
ON v.mes = c.mes AND v.sucursal = 'Sur'
)
SELECT mes, ventas,
LAG(ventas) OVER (ORDER BY mes) AS ventas_anterior,
ventas - LAG(ventas) OVER (ORDER BY mes) AS cambio
FROM sur
ORDER BY mes;In the sandbox, the April through August rows came out like this:
| mes | ventas | ventas_anterior | cambio |
|---|---|---|---|
| 2025-04-01 | 121000 | 112700 | 8300 |
| 2025-05-01 | 119600 | 121000 | -1400 |
| 2025-06-01 | NULL | 119600 | NULL |
| 2025-07-01 | 117600 | NULL | NULL |
| 2025-08-01 | 122900 | 117600 | 5300 |
Now June exists, even though it's empty, and July is no longer compared with May: its change is NULL, which is the honest answer. If you move v.sucursal = 'Sur' from the ON to the WHERE, the June row disappears: WHERE runs after the join and throws out everything that doesn't say Sur. In the sandbox, that version returned 11 rows, with no June.
Interview questions that hide this gap: "find the gaps in this series," "which users went more than 30 days without buying?" or "which days had no sales at all?" The first two are LAG plus a distance; the third is a calendar with a LEFT JOIN. If you say out loud that LAG counts rows, not dates, you've already shown what the question is looking for. If you want to check a series of your own, tell your Maestro what your table looks like.
Before you hand off any "vs. previous period" number, run this page's gap search on your table. If it comes back empty, your LAG compares what it says it compares. If something shows up, decide with whoever reads the report: fill in the calendar, or label it "vs. May."