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

Related

• sql Consecutive groups in SQL using window functions - 2023-06-08
• sql Cumulative total over time in SQL - 2021-09-13
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite The simplest recursive CTE - 2022-03-20
• datasette Querying for GitHub issues open for less than 60 seconds - 2021-03-12
• sqlite Null case comparisons in SQLite - 2020-04-21
• sqlite The SQLite now argument is stable within the same query - 2023-02-05

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