/* Cases by State (for US) */

SELECT state
, confirmed, confirmed_delta, Round(confirmed_increase * 100, 1) || '%' AS confirmed_increase_text
, deaths, deaths_delta, Round(deaths_increase * 100, 1) || '%' AS deaths_increase_text
, Round(mortality_rate * 100, 1) || '%' mortality_rate_text
, confirmed_increase
, deaths_increase
, mortality_rate
FROM (
    SELECT day0.state
    , day0.confirmed, day0.deaths
    , day0.confirmed - day_minus_x.confirmed AS confirmed_delta
    , CASE WHEN day_minus_x.confirmed != 0 THEN (day0.confirmed - day_minus_x.confirmed) / day_minus_x.confirmed ELSE 0 END AS confirmed_increase
    , day0.deaths - day_minus_x.deaths deaths_delta
    , CASE WHEN day_minus_x.deaths > 0 THEN (day0.deaths - day_minus_x.deaths) / day_minus_x.deaths ELSE 0 END AS deaths_increase
    , CASE WHEN day0.confirmed > 0 THEN day0.deaths / day0.confirmed ELSE 0 END AS mortality_rate
    FROM (
        SELECT province_state state
        , SUM( CASE WHEN case_type = 'Confirmed' THEN cases ELSE null END ) confirmed
        , SUM( CASE WHEN case_type = 'Deaths' THEN cases ELSE null END ) deaths
        FROM covid_19_cases
        WHERE country_region = 'US' -- change country/region here
        AND date = '2020-03-31'     -- set end of date range here
        AND table_names = 'Daily Summary'
        GROUP BY province_state
    ) day0 
    INNER JOIN (
        SELECT province_state state
        , SUM( CASE WHEN case_type = 'Confirmed' THEN cases ELSE null END ) confirmed
        , SUM( CASE WHEN case_type = 'Deaths' THEN cases ELSE null END ) deaths
        FROM covid_19_cases
        WHERE country_region = 'US' -- change country/region here
        AND date = '2020-03-30'     -- set start of date range here
        AND table_names = 'Daily Summary'
        GROUP BY province_state
    ) day_minus_x ON day0.state = day_minus_x.state
) a
ORDER BY confirmed DESC
LIMIT 100;



/* Cases Per Capita (for US States) */
/* please note: you will not be able to run this query with just the COVID-19 dataset. You will need to add a dataset for the population as well and then update the query. Or you can request access to our Project as explained in the blog post */
SELECT a.state, p.population
, Round( a.confirmed / p.population * 100000 , 2 ) confirmed_per_100k
, Round( a.deaths / p.population * 100000 , 2 ) deaths_per_100k
, Round(a.mortality_rate * 100, 1) || ' %' mortality_rate_text
, Round(a.mortality_rate, 3) mortality_rate
FROM (
    SELECT day0.state
    , day0.confirmed, day0.deaths
    , CASE WHEN day0.confirmed > 0 THEN day0.deaths / day0.confirmed ELSE 0 END AS mortality_rate
    FROM (
        SELECT province_state state
        , SUM( CASE WHEN case_type = 'Confirmed' THEN cases ELSE null END ) confirmed
        , SUM( CASE WHEN case_type = 'Deaths' THEN cases ELSE null END ) deaths
        FROM covid_19_cases
        WHERE country_region = 'US' -- change country/region here
        AND date = '2020-03-31'     -- set end of date range here
        AND table_names = 'Daily Summary'
        GROUP BY province_state
    ) day0 
) a
INNER JOIN (
    SELECT state, pop population
    FROM data_us_states_population
) p ON a.state = p.state
ORDER BY confirmed_per_100k DESC
LIMIT 100