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:

1
select
2
  day,
3
  confirmed - lag(confirmed, 1) OVER (
4
    ORDER BY
5
      day
6
  ) 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):

1
with italy as (
2
  select
3
    rowid,
4
    day,
5
    country_or_region,
6
    province_or_state,
7
    admin2,
8
    fips,
9
    confirmed,
10
    deaths,
11
    recovered,
12
    active,
13
    latitude,
14
    longitude,
15
    last_update,
16
    combined_key
17
  from
18
    johns_hopkins_csse_daily_reports
19
  where
20
    "country_or_region" = :p0
21
  order by
22
    confirmed desc
23
)
24
select
25
  day,
26
  confirmed - lag(confirmed, 1) OVER (
27
    ORDER BY
28
      day
29
  ) as new_cases
30
from
31
  italy
32
order by day desc limit 50