-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcovid data.sql
116 lines (76 loc) · 3.07 KB
/
covid data.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
select*
from profolio..CovidDeaths
where continent is not null
order by 3,4
select*
from profolio..[Covid vaccination]
order by 3,4
select location,date,total_cases,new_cases,total_deaths,population
from PROFOLIO..CovidDeaths
order by 1,2
--looking at total cases vs total death
--shows the likelyhood of dying
select location,date,total_cases,total_deaths,(total_deaths/total_cases)*100 as deahpercentage
from PROFOLIO..CovidDeaths
where location like'%kenya%'
order by 1,2
--looking at the total cases vs population
select location,date,total_cases,population,(total_cases/population)*100 as deahpercentage
from PROFOLIO..CovidDeaths
where location like'%kenya%'
order by 1,2
--countries with highest infection rate
select location,population,MAX(total_cases)as highestinfectioncount,max((total_cases/population))*100 as percentpopulationunfected
from PROFOLIO..CovidDeaths
--where location like'%kenya%'
group by location,population
order by percentpopulationunfected desc
-- countries with the highest death count
select location,max(cast(total_deaths as int)) as totaldeathcount
from PROFOLIO..CovidDeaths
--where location like'%kenya%'
where continent is null
group by location
order by totaldeathcount desc
--global numbers
select sum(new_cases)as total_cases,sum(cast(new_deaths as int)) as total_deaths,sum(cast(new_deaths as int))/sum(new_cases)*100 as deathpercentage
from PROFOLIO..CovidDeaths
where continent is not null
--group by date
order by 1,2
---looking at total population vs vaccination
select dea.continent,dea.location,dea.date,dea.population,vac.new_vaccinations
,sum(convert(int,vac.new_vaccinations)) over (partition by dea.location order by dea.location,dea.date)as rollingpeoplevaccinated
--,(rollingpeoplevaccinated/population)*100
from PROFOLIO..[Covid vaccination] vac
join PROFOLIO..CovidDeaths dea
on vac.location =dea.location
and vac.date =dea.date
where dea.continent is not null
order by 2,3
--use cte
with popvsvac(continent,location,date,population, new_vaccination,rollingpeoplevaccinated)
as
(
select dea.continent,dea.location,dea.date,dea.population,vac.new_vaccinations
,sum(convert(int,vac.new_vaccinations)) over (partition by dea.location order by dea.location,dea.date)as rollingpeoplevaccinated
--,(rollingpeoplevaccinated/population)*100
from PROFOLIO..[Covid vaccination] vac
join PROFOLIO..CovidDeaths dea
on vac.location =dea.location
and vac.date =dea.date
where dea.continent is not null
--order by 2,3
)
select*,(rollingpeoplevaccinated/population)*100
from popvsvac
create view percentpopulationvaccinated as
select dea.continent,dea.location,dea.date,dea.population,vac.new_vaccinations
,sum(convert(int,vac.new_vaccinations)) over (partition by dea.location order by dea.location,dea.date)as rollingpeoplevaccinated
--,(rollingpeoplevaccinated/population)*100
from PROFOLIO..[Covid vaccination] vac
join PROFOLIO..CovidDeaths dea
on vac.location =dea.location
and vac.date =dea.date
where dea.continent is not null
--order by 2,3