-
Notifications
You must be signed in to change notification settings - Fork 0
/
Covid-19 Data Analysis.sql
191 lines (149 loc) · 6.55 KB
/
Covid-19 Data Analysis.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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
-- Viewing the available data
SELECT location, date, total_cases, new_cases, total_deaths, population
FROM public."CovidDeaths"
WHERE continent IS NOT NULL
ORDER BY location, date;
-- Considering Total Deaths vs Total Cases (Worldwide)
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS death_percentage
FROM public."CovidDeaths"
WHERE continent IS NOT NULL
ORDER BY location, date;
-- Considering Total Deaths vs Total Cases (India)
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS death_percentage
FROM public."CovidDeaths"
WHERE location = 'India'
ORDER BY location, date;
-- Considering Total Cases vs Population (Worldwide)
SELECT location, date, total_cases, population, (total_cases/population)*100 AS cases_percentage
FROM public."CovidDeaths"
WHERE continent IS NOT NULL
ORDER BY location, date;
-- Considering Total Cases vs Population (India)
SELECT location, date, total_cases, population, (total_cases/population)*100 AS cases_percentage
FROM public."CovidDeaths"
WHERE location = 'India'
ORDER BY location, date;
-- Highest infection rates with respect to population
SELECT location, population, MAX(total_cases) AS highest_infection_count, (MAX(total_cases)/population)*100 AS infected_percentage
FROM public."CovidDeaths"
WHERE continent IS NOT NULL
GROUP BY location, population
ORDER BY infected_percentage DESC NULLS LAST;
-- Highest death counts with respect to population
SELECT location, MAX(total_deaths) AS total_death_count
FROM public."CovidDeaths"
WHERE continent IS NOT NULL
GROUP BY location
ORDER BY total_death_count DESC NULLS LAST;
-- Continent-wise highest death counts
SELECT location, MAX(total_deaths) AS total_death_count
FROM public."CovidDeaths"
WHERE continent IS NULL AND location NOT IN ('World', 'European Union')
GROUP BY location
ORDER BY total_death_count DESC NULLS LAST;
-- Continent-wise highest death counts with respect to population
SELECT location, population, MAX(total_deaths)AS total_death_count, (MAX(total_deaths)/population)*100 AS deaths_percentage
FROM public."CovidDeaths"
WHERE continent IS NULL AND location NOT IN ('World', 'European Union')
GROUP BY location, population
ORDER BY deaths_percentage DESC NULLS LAST;
--Global death by cases percentage
SELECT date, SUM(new_cases) AS total_cases, SUM(new_deaths) AS total_deaths,
(SUM(new_deaths)/NULLIF(SUM(new_cases),0))*100 AS death_percentage
FROM public."CovidDeaths"
WHERE continent IS NULL AND location = 'World'
GROUP BY date
ORDER BY date DESC NULLS LAST;
-- JOINING deaths and vaccinations tables
SELECT *
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.date = vaccs.date
ORDER BY deaths.date;
-- Total populations vs Vaccinations
-- Including date-by-date RollingVaccinated
SELECT deaths.continent, deaths.location, deaths.date, deaths.population, vaccs.new_vaccinations,
SUM(vaccs.new_vaccinations) OVER (PARTITION BY deaths.location ORDER BY deaths.location, deaths.date)
AS rolling_people_vaccinated
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
AND deaths.date = vaccs.date
WHERE deaths.continent IS NOT NULL
ORDER BY deaths.location, deaths.date;
-- USE CTE
With PopvsVacc (continent, location, date, population, new_vaccinations, rolling_people_vaccinated)
as
(
SELECT deaths.continent, deaths.location, deaths.date, deaths.population, vaccs.new_vaccinations,
SUM(vaccs.new_vaccinations) OVER (PARTITION BY deaths.location ORDER BY deaths.location, deaths.date)
AS rolling_people_vaccinated
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
AND deaths.date = vaccs.date
WHERE deaths.continent IS NOT NULL
)
SELECT *, (rolling_people_vaccinated/population)*100 AS rolling_vaccinated_percentage
FROM PopvsVacc
-- TEMP Table
-- IN case modification to table needed,
-- DROP TABLE IF EXISTS PercentPopulationVaccinated
CREATE TABLE PercentPopulationVaccinated
(
continent varchar (255),
location varchar (255),
date date,
population bigint,
new_vaccinations bigint,
rolling_people_vaccinated numeric
);
INSERT INTO PercentPopulationVaccinated
(
SELECT deaths.continent, deaths.location, deaths.date, deaths.population, vaccs.new_vaccinations,
SUM(vaccs.new_vaccinations) OVER (PARTITION BY deaths.location ORDER BY deaths.location, deaths.date)
AS rolling_people_vaccinated
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
AND deaths.date = vaccs.date
WHERE deaths.continent IS NOT NULL
);
SELECT *, (rolling_people_vaccinated/population)*100 AS rolling_vaccinated_percentage
FROM PercentPopulationVaccinated;
-- Creating a view to store data for later visualization
CREATE VIEW PercentPopuationVaccinated AS
SELECT deaths.continent, deaths.location, deaths.date, deaths.population, vaccs.new_vaccinations,
SUM(vaccs.new_vaccinations) OVER (PARTITION BY deaths.location ORDER BY deaths.location, deaths.date)
AS rolling_people_vaccinated
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
AND deaths.date = vaccs.date
WHERE deaths.continent IS NOT NULL;
SELECT *
FROM PercentPopulationVaccinated;
-- Analysis of hospital bed capacity country wise
SELECT location, MAX(hospital_beds_per_thousand) AS hosp_beds_per_thousand
FROM public."CovidVaccinations"
WHERE continent IS NOT NULL
GROUP BY location
ORDER BY MAX(hospital_beds_per_thousand) DESC NULLS LAST;
-- Comparing Country's death rate with its GDP
SELECT deaths.location, deaths.population, MAX(deaths.total_deaths) AS total_death_count,
(MAX(deaths.total_deaths)/population)*100 AS deaths_percentage, vaccs.gdp_per_capita
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
WHERE deaths.continent IS NOT NULL
GROUP BY deaths.location, deaths.population, vaccs.gdp_per_capita
ORDER BY vaccs.gdp_per_capita DESC NULLS LAST;
-- Comparing Country's death rate with its HDI
SELECT deaths.location, deaths.population, MAX(deaths.total_deaths) AS total_death_count,
(MAX(deaths.total_deaths)/population)*100 AS deaths_percentage, vaccs.human_development_index
FROM public."CovidDeaths" AS deaths
JOIN public."CovidVaccinations" AS vaccs
ON deaths.location = vaccs.location
WHERE deaths.continent IS NOT NULL
GROUP BY deaths.location, deaths.population, vaccs.human_development_index
ORDER BY vaccs.human_development_index DESC NULLS LAST;