Skip to content

Instantly share code, notes, and snippets.

@djouallah

djouallah/covid.sql

Created March 26, 2020 12:07
Show Gist options
  • Save djouallah/dcbb10236742a17e2d944bb989685850 to your computer and use it in GitHub Desktop.
Save djouallah/dcbb10236742a17e2d944bb989685850 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
covid.sql
with tablename as(SELECT country_region,date,sum(deaths) as deaths,sum(confirmed) as confirmed FROM `bigquery-public-data.covid19_jhu_csse.summary`
group by 1,2 )
select t.*, deaths - coalesce(lag(deaths) over(partition by country_region order by date),0) as daily_deaths,
confirmed - coalesce(lag(confirmed) over(partition by country_region order by date),0) as daily_confirmed
from tablename t
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment