search

simonw / covid-19-datasette

Deploys a Datasette instance of COVID-19 data from Johns Hopkins CSSE and the New York Times
https://covid-19.datasettes.com/
61 stars 6 forks source link

Create a view showing deaths/cases per million for US counties #19

Closed simonw closed 4 years ago

simonw commented 4 years ago

I'm going to turn this into a view. Originally posted by @simonw in https://github.com/simonw/covid-19-datasette/issues/18#issuecomment-659078398

simonw commented 4 years ago

Example query: https://covid-19.datasettes.com/covid?sql=select%0D%0A++ny_times_us_counties.date%2C%0D%0A++ny_times_us_counties.county%2C%0D%0A++ny_times_us_counties.state%2C%0D%0A++ny_times_us_counties.fips%2C%0D%0A++ny_times_us_counties.cases%2C%0D%0A++ny_times_us_counties.deaths%2C%0D%0A++us_census_county_populations_2019.population%2C%0D%0A++1000000+*+ny_times_us_counties.deaths+%2F+us_census_county_populations_2019.population+as+deaths_per_million%2C%0D%0A++1000000+*+ny_times_us_counties.cases+%2F+us_census_county_populations_2019.population+as+cases_per_million%0D%0Afrom%0D%0A++ny_times_us_counties%0D%0A++join+us_census_county_populations_2019+on+ny_times_us_counties.fips+%3D+us_census_county_populations_2019.fips%0D%0Awhere%0D%0A++%22date%22+%3D+%28select+max%28date%29+from+ny_times_us_counties%29%0D%0Aorder+by%0D%0A++deaths_per_million+desc%0D%0A

date county state fips cases deaths population deaths_per_million cases_per_million
2020-07-14 Hancock Georgia 13141 236 33 8457 3902 27905
2020-07-14 Randolph Georgia 13243 212 25 6778 3688 31277
2020-07-14 Terrell Georgia 13273 249 28 8531 3282 29187
2020-07-14 Early Georgia 13099 313 31 10190 3042 30716
2020-07-14 Emporia city Virginia 51595 146 15 5346 2805 27310
2020-07-14 McKinley New Mexico 35031 3679 192 71367 2690 51550
2020-07-14 Neshoba Mississippi 28099 1050 77 29118 2644 36060
2020-07-14 Essex New Jersey 34013 19307 2078 798975 2600 24164
2020-07-14 Lowndes Alabama 1085 502 25 9726 2570 51614
simonw commented 4 years ago

I'll call this view latest_ny_times_counties_with_populations


create view
  latest_ny_times_counties_with_populations as
select
  ny_times_us_counties.date,
  ny_times_us_counties.county,
  ny_times_us_counties.state,
  ny_times_us_counties.fips,
  ny_times_us_counties.cases,
  ny_times_us_counties.deaths,
  us_census_county_populations_2019.population,
  1000000 * ny_times_us_counties.deaths / us_census_county_populations_2019.population as deaths_per_million,
  1000000 * ny_times_us_counties.cases / us_census_county_populations_2019.population as cases_per_million
from
  ny_times_us_counties
  join us_census_county_populations_2019 on ny_times_us_counties.fips = us_census_county_populations_2019.fips
where
  "date" = (
    select
      max(date)
    from
      ny_times_us_counties
  )
order by
  deaths_per_million desc
simonw commented 4 years ago

https://covid-19.datasettes.com/covid/latest_ny_times_counties_with_populations