-
Notifications
You must be signed in to change notification settings - Fork 1
/
covid_quadrant_map.sql
46 lines (46 loc) · 2.42 KB
/
covid_quadrant_map.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
--County Data with Populations, Deaths and Cases
WITH
cases AS (
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_CASES,
DIV0(COUNTY_CASES,GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY,
CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE('2020-11-21') AND JHU.CASE_TYPE IN('Confirmed')
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE
ORDER BY JHU.FIPS
),
deaths AS(
SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION,
SUM(JHU.CASES) AS COUNTY_DEATHS,
DIV0(COUNTY_DEATHS,GEOSQL.COUNTY_POPULATION) AS DEATHS_PER_CAPITA_COUNTY,
DEATHS_PER_CAPITA_COUNTY * 100000 AS DEATHS_PER_100K
FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU
LEFT JOIN (
SELECT LEFT(CBG,5) AS FIPS, SUM(GEO.TOTAL_POPULATION) AS COUNTY_POPULATION
FROM SAFEGRAPH_SAFEGRAPH_SHARE.PUBLIC.US_POPULATION_BY_SEX_GEO GEO GROUP BY FIPS) GEOSQL
ON GEOSQL.FIPS = JHU.FIPS
WHERE JHU.COUNTRY_REGION = 'United States' AND JHU.DATE = TO_DATE('2020-11-21') AND JHU.CASE_TYPE IN('Deaths')
GROUP BY JHU.PROVINCE_STATE, JHU.COUNTY, JHU.FIPS, GEOSQL.COUNTY_POPULATION, JHU.DATE, JHU.CASE_TYPE
ORDER BY JHU.FIPS
)
SELECT
cases.*,
deaths.COUNTY_DEATHS,
deaths.DEATHS_PER_CAPITA_COUNTY,
deaths.DEATHS_PER_100K
FROM cases
LEFT JOIN deaths on cases.FIPS = deaths.FIPS
WHERE cases.COUNTY <> 'unassigned' AND cases.FIPS IS NOT NULL
AND cases.PROVINCE_STATE IN ('Alabama','Alaska','Arizona','Arkansas','California'
,'Colorado','Connecticut','Delaware','District of Columbia','Florida','Georgia','Hawaii'
,'Idaho','Illinois','Indiana','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland'
,'Massachusetts','Michigan','Minnesota','Mississippi','Missouri','Montana','Nebraska'
,'Nevada','New Hampshire','New Jersey','New Mexico','New York','North Carolina'
,'North Dakota','Ohio','Oklahoma','Oregon','Pennsylvania','Rhode Island','South Carolina'
,'South Dakota','Tennessee','Texas','Utah','Vermont','Virgin Islands','Virginia'
,'Washington','West Virginia','Wisconsin','Wyoming');