/
schema.sql
116 lines (105 loc) · 3.16 KB
/
schema.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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
CREATE TABLE "states" (
date DATE,
state TEXT,
fips NUMERIC,
cases NUMERIC,
deaths NUMERIC
);
SELECT create_hypertable('states', 'date', 'state', 2, create_default_indexes=>FALSE);
CREATE INDEX ON states (date ASC, state);
CREATE TABLE "counties" (
date DATE,
county TEXT,
state TEXT,
fips NUMERIC,
cases NUMERIC,
deaths NUMERIC
);
SELECT create_hypertable('counties', 'date', 'county', 2, create_default_indexes=>FALSE);
CREATE INDEX ON counties (date ASC, county);
CREATE VIEW northern_california AS
SELECT date, fips, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM counties
WHERE county IN ('San Francisco', 'Santa Clara', 'Alameda', 'Marin', 'San Mateo', 'Contra Costa') AND state = 'California'
GROUP BY date, fips
ORDER BY date DESC;
CREATE VIEW southern_california AS
SELECT date, fips, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM counties
WHERE county IN ('Los Angeles', 'Ventura', 'Orange', 'San Bernardino', 'Riverside') AND state = 'California'
GROUP BY date, fips
ORDER BY date DESC;
CREATE VIEW new_york_city AS
SELECT date, fips, sum(cases) as total_cases, sum(deaths) as total_deaths
FROM counties
WHERE county IN ('New York City', 'Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island') AND state = 'New York'
GROUP BY date, fips
ORDER BY date desc;
-- What about anticipated Election 2020 battleground counties?
CREATE VIEW battleground_counties AS
SELECT date, fips, state, county, sum(cases) as total_cases, sum(deaths) as total_deaths
FROM counties
WHERE
(county IN ('Erie') AND state = 'Pennsylvania') OR
(county IN ('Saulk') AND state = 'Wisconsin') OR
(county IN ('Muskegon') AND state = 'Michigan') OR
(county in ('Maricopa') AND state = 'Arizona') OR
(county IN ('Tarrant') AND state = 'Texas') OR
(county IN ('New Hanover') AND state = 'North Carolina') OR
(county IN ('Peach') AND state = 'Georgia') OR
(county IN ('Washington') AND state = 'Minnesota') OR
(county IN ('Hillsborough') AND state = 'New Hampshire') OR
(county IN ('Lincoln') AND state = 'Maine')
GROUP BY date, fips, state, county
ORDER BY date desc;
CREATE TABLE "world" (
date DATE,
country TEXT,
cases NUMERIC,
recovered NUMERIC,
deaths NUMERIC
);
SELECT create_hypertable('world', 'date', 'country', 2, create_default_indexes=>FALSE);
CREATE INDEX ON world (date ASC, country);
CREATE TABLE "world_static_data" (
city TEXT,
latitude NUMERIC,
longitude NUMERIC,
country TEXT,
population NUMERIC
);
CREATE TABLE "employment" (
fips NUMERIC,
date DATE,
population NUMERIC,
employed NUMERIC,
unemployed NUMERIC,
unemploymentrate NUMERIC
);
CREATE TABLE "elections" (
year NUMERIC,
state TEXT,
state_abbreviation TEXT,
county TEXT,
fips NUMERIC,
office TEXT,
candidate TEXT,
party TEXT,
votes NUMERIC,
total_votes NUMERIC,
version TEXT
);
CREATE TABLE "gdp" (
fips NUMERIC,
county TEXT,
dollars NUMERIC
);
CREATE TABLE "population" (
statecode NUMERIC,
countycode NUMERIC,
fips NUMERIC,
state TEXT,
county TEXT,
population2010 NUMERIC,
population2019 NUMERIC
);