-
Notifications
You must be signed in to change notification settings - Fork 2
/
Covid-19 Worldwide.sql
154 lines (130 loc) · 5.89 KB
/
Covid-19 Worldwide.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
/*
COVID-19 Worldwide Data Exloration
Using - Joins, CTE, Temp Tables, Windows functions, Aggregate functions, creating views, Converting data types
*/
-- 1) What is the Mortality rate - total deaths divided by total cases
SELECT continent, location, date, total_cases, total_deaths, (total_deaths * 1.0 /total_cases) * 100 as mortality_rate
FROM coviddeaths
WHERE Continent is not NULL
order by 1,2
-- 2) What percentage of the population got covid
SELECT continent, location, date, total_cases, population, (total_cases * 1.0 /population) * 100 as PercentPopulationInfected
FROM coviddeaths
WHERE Continent is not NULL
order by 1,2
-- 3) What country has the highest infection rate compared to population
SELECT continent, location, population, Max(total_cases) as highestInfectionCount, MAX((total_cases * 1.0/population)*100) as PercentPopulationInfected
FROM coviddeaths
WHERE Continent is not NULL
Group by continent, location, population
order by 4 desc
-- 4) What country has the highest death rate per population
SELECT continent, location, population, MAX(total_deaths) as hightestDeathCount, MAX((total_deaths * 1.0/population)*100) as PercentPopulationDied
FROM coviddeaths
WHERE Continent is not NULL
Group by continent, location, population
order by 4 desc
-- 5) What country has the highest death count
SELECT continent, location, MAX(total_deaths) as hightestDeathCount
FROM coviddeaths
WHERE CONTINENT IS NOT NULL
Group by continent, location
order by hightestDeathCount desc
-- 6) What continent has the highest death count
SELECT continent, MAX(total_deaths) as hightestDeathCount
FROM coviddeaths
WHERE CONTINENT IS NOT NULL
Group by continent
order by hightestDeathCount desc
-- 7) What are the global cases for each day
SELECT date, SUM(new_cases) as total_newcases, sum(new_deaths) as total_newdeaths,
case
WHEN SUM(new_cases) <> 0 THEN SUM(new_deaths)*1.0/SUM(new_cases)*100
ELSE NULL
END AS death_rate
FROM coviddeaths
WHERE Continent is not NULL
GROUP BY DATE
Order by date
-- 8) What is the rolling count of people vaccinated, meaning after each day what is the total number of vaccinated people
-- using CTE
WITH PopVsVac (continent, location, date, population, new_vaccinations, RollingCountofPeopleVaccinated)
AS
(SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations, SUM(CAST(new_vaccinations AS BIGINT))
OVER (PARTITION BY dea.location order by dea.location, dea.date) AS RollingCountofPeopleVaccinated
FROM coviddeaths dea
JOIN covidvaccinations vac
ON dea.location = vac.location AND dea.date = vac.date
where dea.continent IS NOT NULL)
SELECT *, (RollingCountofPeopleVaccinated*1.0/population) * 100 AS PercentageofVaccinatedPeople
FROM PopVsVac
-- 9) What is the rolling count of people vaccinated, meaning after each day what is the total number of vaccinated people
-- using TempTable
DROP TABLE IF EXISTS #PercentagePopulationVaccinated
Create Table #PercentagePopulationVaccinated
(continent NVARCHAR(255),
location NVARCHAR(255),
date DATE,
population NUMERIC,
new_vaccinations NUMERIC,
RollingCountofPeopleVaccinated NUMERIC
)
INSERT INTO #PercentagePopulationVaccinated
SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations, SUM(CAST(new_vaccinations AS BIGINT))
OVER (PARTITION BY dea.location order by dea.location, dea.date) AS RollingCountofPeopleVaccinated
FROM coviddeaths dea
JOIN covidvaccinations vac
ON dea.location = vac.location AND dea.date = vac.date
where dea.continent IS NOT NULL
SELECT *, (RollingCountofPeopleVaccinated*1.0/population) * 100 AS PercentageofVaccinatedPeople
FROM #PercentagePopulationVaccinated
ORDER BY 2,3
-- 10) Create views to store our results and later use for visualizations
Create View mortalityrate AS
SELECT continent, location, date, total_cases, total_deaths, (total_deaths * 1.0 /total_cases) * 100 as mortality_rate
FROM coviddeaths
WHERE Continent is not NULL
Create View PercentagePopulationInfected AS
SELECT continent, location, date, total_cases, population, (total_cases * 1.0 /population) * 100 as PercentPopulationInfected
FROM coviddeaths
WHERE Continent is not NULL
Create View HighestInfectedCountry AS
SELECT continent, location, population, Max(total_cases) as highestInfectionCount, MAX((total_cases * 1.0/population)*100) as PercentPopulationInfected
FROM coviddeaths
WHERE Continent is not NULL
Group by continent, location, population
Create View HighestDeathperPopulation AS
SELECT continent, location, population, MAX(total_deaths) as hightestDeathCount, MAX((total_deaths * 1.0/population)*100) as PercentPopulationDied
FROM coviddeaths
WHERE Continent is not NULL
Group by continent, location, population
Create View hightestDeathCountLocation AS
SELECT continent, location, MAX(total_deaths) as hightestDeathCount
FROM coviddeaths
WHERE CONTINENT IS NOT NULL
Group by continent, location
Create View HighestDeathCountContinent AS
SELECT continent, MAX(total_deaths) as hightestDeathCount
FROM coviddeaths
WHERE CONTINENT IS NOT NULL
Group by continent
Create View GlobalCasesPerDay AS
SELECT date, SUM(new_cases) as total_newcases, sum(new_deaths) as total_newdeaths,
case
WHEN SUM(new_cases) <> 0 THEN SUM(new_deaths)*1.0/SUM(new_cases)*100
ELSE NULL
END AS death_rate
FROM coviddeaths
WHERE Continent is not NULL
GROUP BY DATE
Create View RollingCountofPeopleVaccinated AS
WITH PopVsVac (continent, location, date, population, new_vaccinations, RollingCountofPeopleVaccinated)
AS
(SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations, SUM(CAST(new_vaccinations AS BIGINT))
OVER (PARTITION BY dea.location order by dea.location, dea.date) AS RollingCountofPeopleVaccinated
FROM coviddeaths dea
JOIN covidvaccinations vac
ON dea.location = vac.location AND dea.date = vac.date
where dea.continent IS NOT NULL)
SELECT *, (RollingCountofPeopleVaccinated*1.0/population) * 100 AS PercentageofVaccinatedPeople
FROM PopVsVac