Comparing a period to the one before it

Comparing a period to the one before it comes up often enough to be worth a note. city-air-quality is a convenient thing to try it on — 5,480 rows, one row per city per day.

with daily as (
  select date_trunc('day', reading_date) as day, sum(pm25) as total
  from city_air_quality
  group by 1
)
select day,
       total,
       lag(total, 7) over (order by day)                       as same_day_last_week,
       total - lag(total, 7) over (order by day)               as delta
from daily
order by day;

Lagging by seven rather than one is deliberate: a day-over-day comparison on almost any business metric is mostly measuring which day of the week it is. If the series has missing days, lag(7) silently compares the wrong pair — which is the argument for a date spine underneath it.

Run it against the real file in the SQL playground — the engine there is enough of a SQL implementation to execute this as written, and the dataset is already loaded. The pattern page has the version with the failure modes spelled out.

Window functions are the difference between SQL that describes rows and SQL that describes sequences. Almost every "we exported it to pandas to do this bit" turns out to be one of these five.