-
Notifications
You must be signed in to change notification settings - Fork 0
/
Covid Monitoring Data Exploration.sql
111 lines (85 loc) · 3.49 KB
/
Covid Monitoring Data Exploration.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
/*
Covid 19 Data Exploration
Skills used: Joins, CTE's, Temp Tables, Windows Functions, Aggregate Functions, Creating Views, Converting Data Types
*/
Select *
From coviddeaths
Where continent is not null
order by 3,4;
-- Select Data that we are going to be starting with
Select Location, date, total_cases, new_cases, total_deaths, population
From coviddeaths
Where continent is not null
order by 1,2;
-- Total Cases vs Population
-- Shows what percentage of population infected with Covid
Select Location, date, Population, total_cases, (total_cases/population)*100 as PercentPopulationInfected
From coviddeaths
-- Where location like '%south africa%'
order by 1,2;
-- Countries with Highest Infection Rate compared to Population
Select Location, Population, MAX(total_cases) as HighestInfectionCount, Max((total_cases/population))*100 as PercentPopulationInfected
From coviddeaths
-- Where location like '%south africa%'
Where continent is not null
Group by Location, Population
order by PercentPopulationInfected desc;
-- Countries with Highest Death Count per Population
Select Location, MAX(CAST(total_deaths as unsigned)) as TotalDeathCount
From coviddeaths
-- Where location like '%south africa%'
Where continent is not null
Group by Location
order by TotalDeathCount desc;
-- BREAKING THINGS DOWN BY CONTINENT
-- Showing contintents with the highest death count per population
Select continent, MAX(cast(Total_deaths as unsigned)) as TotalDeathCount
From CovidDeaths
-- Where location like '%south africa%'
Where continent is not null
Group by continent
order by TotalDeathCount desc;
-- GLOBAL NUMBERS
Select SUM(new_cases) as total_cases, SUM(cast(new_deaths as unsigned)) as total_deaths, SUM(cast(new_deaths as unsigned))/SUM(New_Cases)*100 as DeathPercentage
From coviddeaths
-- Where location like '%south africa%'
where continent is not null
-- Group By date
order by 1,2;
-- Total Population vs Vaccinations
-- Shows Percentage of Population that has recieved at least one Covid Vaccine
Select dea.continent, dea.location, dea.date, dea.population, vac.new_vacc
, SUM(CAST(vac.new_vacc as unsigned)) OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
-- , (RollingPeopleVaccinated/population)*100
From coviddeaths dea
Join covidvaccination2 vac
On dea.location = vac.location
and dea.date = vac.date
where dea.continent is not null
order by 2,3;
-- Using CTE to perform Calculation on Partition By in previous query
With PopvsVac (Continent, Location, Date, Population, New_Vaccinations, RollingPeopleVaccinated)
as
(
Select dea.continent, dea.location, dea.date, dea.population, vac.new_vacc
, SUM(CAST(vac.new_vacc as unsigned)) OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
-- , (RollingPeopleVaccinated/population)*100
From coviddeaths dea
Join covidvaccination2 vac
On dea.location = vac.location
and dea.date = vac.date
where dea.continent is not null
-- order by 2,3
)
Select *, (RollingPeopleVaccinated/Population)*100
From PopvsVac;
-- Creating View to store data for later visualizations
Create View PercentPopulationVaccinated as
Select dea.continent, dea.location, dea.date, dea.population, vac.new_vacc
, SUM(CAST(vac.new_vacc as unsigned)) OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
-- , (RollingPeopleVaccinated/population)*100
From coviddeaths dea
Join covidvaccination2 vac
On dea.location = vac.location
and dea.date = vac.date
where dea.continent is not null