Lag window function in SQLite

Here's how to use a lag window function to calculate new cases per day when the table just has total cases over time on different dates.

The key clause is this:

select
  day,
  confirmed - lag(confirmed, 1) OVER (
    ORDER BY
      day
  ) as new_cases

So the syntax is lag(column, 1) over (order by day) - to get the previous value of column based on the day.

Full example query (using a CTE as well):

with italy as (
  select
    rowid,
    day,
    country_or_region,
    province_or_state,
    admin2,
    fips,
    confirmed,
    deaths,
    recovered,
    active,
    latitude,
    longitude,
    last_update,
    combined_key
  from
    johns_hopkins_csse_daily_reports
  where
    "country_or_region" = :p0
  order by
    confirmed desc
)
select
  day,
  confirmed - lag(confirmed, 1) OVER (
    ORDER BY
      day
  ) as new_cases
from
  italy
order by day desc limit 50

Originally tweeted here: https://twitter.com/simonw/status/1246482954630492200

Created 2020-04-19T16:23:04-07:00 · Edit

Lag window function in SQLite

Related