Malek Damiri

LAG compares to the previous row, not the previous month

·

lag() is the standard way to compute month-over-month change, and it's correct as long as every month has a row. When one is missing, it compares to whatever row came before, and the report never says so.

A missing March

create table monthly_revenue as select * from (values
  (date '2026-01-01', 10000.00), (date '2026-02-01', 11000.00), (date '2026-04-01', 12100.00)
) t(month, revenue);

select month, revenue,
       lag(revenue) over (order by month) as prior_revenue,
       round(revenue / lag(revenue) over (order by month) - 1, 3) as mom_change
from monthly_revenue
order by month;
month        revenue    prior_revenue   mom_change
2026-01-01   10000.00            null         null
2026-02-01   11000.00        10000.00          0.1
2026-04-01   12100.00        11000.00          0.1

April shows 10% month-over-month growth. It's actually 10% over two months, measured against February, and nothing on the chart says March is missing.

Fill the months first

with months as (
  select cast(m as date) as month
  from generate_series(date '2026-01-01', date '2026-04-01', interval 1 month) as g(m)
), filled as (
  select m.month, r.revenue
  from months as m
  left join monthly_revenue as r on r.month = m.month
)
select month, revenue,
       lag(revenue) over (order by month) as prior_revenue,
       round(revenue / lag(revenue) over (order by month) - 1, 3) as mom_change
from filled
order by month;
month        revenue    prior_revenue   mom_change
2026-01-01   10000.00            null         null
2026-02-01   11000.00        10000.00          0.1
2026-03-01       null        11000.00         null
2026-04-01   12100.00            null         null

Now March is visibly empty and April has no month-over-month number, which is the honest answer. Whether March should be zero or "not loaded yet" is worth finding out before anyone fills it in.

The same rule applies to lead(), to year-over-year with lag(revenue, 12), and to anything else that counts rows instead of periods: give it a complete calendar first. The date spine note covers the daily version.

Every query here ran in DuckDB 1.5.1 and returned the output shown. This note was written with AI assistance.