More Covid-19 public dataset exploration
This time US data
My Bigquery trial is at around 33% done, so I figured I should make the most of it.
I never saw this data before so I I will make some basic queries which will help me get familiar with it. After that I will think about some interesting questions and answers.
What tables are in the dataset?
How up to date is this dataset? How many days are in it?
SELECT min(date), max(date)
FROM `bigquery-public-data.covid19_nyt.us_states`
Returns from 2020-01-21 to 2020-12-08, so it is updated.
Let's see for example: death ratios by state.
SELECT state_name, round((deaths/confirmed_cases) * 100, 2) as ratio
FROM `bigquery-public-data.covid19_nyt.us_states`
where date in (select max(date) from `bigquery-public-data.covid19_nyt.us_states`)
order by (deaths/confirmed_cases) desc
Why are there 55 states? There are some interesting values here like Guam.
So we have 2 codes by county and state, and no way to put them together. Lets get creative.
create table `angelic-edition-295317.Queries.countyflipscodes`
as (
SELECT distinct county, state_name, county_fips_code FROM `bigquery-public-data.covid19_nyt.us_counties`
where county_fips_code is not null -- null values, a lot of them
)
There, now we have a somewhat useable county code table.
