-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdata_visualization.sql
97 lines (80 loc) · 2.15 KB
/
data_visualization.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
-- Tableau project queries
--Table 1-
-- Finding total infected, total covid deaths and total death percentage globally
SELECT
SUM(total_cases) AS total_infected,
SUM(total_deaths)AS total_CovidDeaths,
SUM(total_deaths)/SUM(total_cases)*100 AS DeathPercentage
FROM
Covid_data.CovidDeaths
WHERE
continent IS NOT NULL
-- Table 2-
-- Total Death counts per location( continent)
-- We take some locations out as they are not included in the above queries and want to stay consistent
SELECT
location,
SUM(total_deaths) as total_death_count
FROM
Covid_data.CovidDeaths
WHERE
continent IS NULL
AND
location NOT IN ('World','High income','Upper middle income','Lower middle income','Low income', 'European Union')
GROUP BY
location
ORDER BY
total_death_count DESC
-- Table 3-
--Locations with highest infection rate compared to the population
SELECT
location,
population,
Max(total_cases) AS highest_infection_count,
Max((total_cases/population))*100 AS percent_population_infected
FROM
Covid_data.CovidDeaths
--WHERE
--Continent IS NOT NULL
GROUP BY
location, population
ORDER BY
percent_population_infected DESC
-- Table 4-
----North America countries highest infection rate compared to the population over time
SELECT
location,
date,
population,
Max(total_cases) AS highest_infection_count,
Max((total_cases/population))*100 AS percent_population_infected
FROM
Covid_data.CovidDeaths
WHERE
Continent = "North America"
GROUP BY
location, population,date
ORDER BY
percent_population_infected DESC
-- Table 5
--To find the max percentage of population who are fully vaccinated
SELECT
dea.location,
dea.date,
dea.population,
Max(vac.people_fully_vaccinated) AS maximum_people_fully_vaccinated,
Max(vac.people_fully_vaccinated/population)*100 As percent_people_fully_vaccinated
FROM
Covid_data.CovidDeaths AS dea
JOIN
Covid_data.CovidVaccinations AS vac
ON dea.location = vac.location
AND dea.date = vac.date
WHERE
dea.continent='North America'
GROUP BY
dea.location,
dea.date,
dea.population
ORDER BY
dea.location, dea.date DESC