__STYLES__
This project analyzes the global effect of COVID in different countries and it's spread since it's appearance until from 2020-2022. It contains the data on number of deaths due to covid and the effect of covid vaccinations from around the world. Here's the link to the global open source COVID data available to public.
After downloading the data, I used Excel & Microsoft SQL Server to Clean, process and analyze the data.
--Calculate Total cases vs Total Deaths --shows the likelihood of dying from covid in United States
SELECT
Location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS DeathPercentage
FROM PortfolioProject.dbo.CovidDeaths$
WHERE location like '%states%'
order by 1,2
--Calculate Total cases vs Population
--shows the percentage of COVID infected people in USA
SELECT
Location, date, total_cases, population, (total_cases/population)*100 as InfectedPercentage
FROM PortfolioProject.dbo.CovidDeaths$
WHERE location like '%states%'
order by 1,2
--Countries with highest infected percentage of population
SELECT
Location, population, MAX(total_cases) as HighestInfectionCount, Max((total_cases/population))*100 as InfectedPercentage
FROM PortfolioProject.dbo.CovidDeaths$
GROUP by Location, population
ORDER BY InfectedPercentage desc
--Continents with highest Death count
SELECT
continent, MAX(cast(total_deaths as int)) as TotalDeathCount
FROM PortfolioProject.dbo.CovidDeaths$
WHERE continent is NOT NULL
GROUP BY continent
ORDER BY TotalDeathCount DESC
--Global Numbers
SELECT date, SUM(new_cases) as global_cases, SUM(cast(new_deaths as int)) as global_deaths,
(SUM(cast(new_deaths as int))/SUM(new_cases))*100 as globalDeathPercentage
FROM PortfolioProject.dbo.CovidDeaths$
WHERE continent is NOT NULL
GROUP BY date
ORDER BY 1,2
--Calculate rolling count of people getting vaccinated each day. Use CTE to calculate vaccinated population
WITH PopVsVacPop (continent, location, date, population, new_vaccinations, TotalPeopleVaccinated)
AS
(
SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CAST(vac.new_vaccinations as bigint)) OVER (Partition by dea.location ORDER BY dea.location,
dea.date) AS TotalPeopleVaccinated
FROM PortfolioProject.dbo.CovidDeaths$ dea
JOIN PortfolioProject.dbo.CovidVaccinations$ vac
ON dea.location = vac.location
AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
)
SELECT *, (TotalPeopleVaccinated/population)*100
FROM PopVsVacPop
--Create TEMPORARY Table
CREATE TABLE #PercentPopulationVaccinated
(
Continent nvarchar(255),
Location nvarchar(255),
Date datetime,
Population numeric,
New_vaccinations numeric,
TotalPeopleVaccinated numeric
)
INSERT INTO #PercentPopulationVaccinated
SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CAST(vac.new_vaccinations as bigint)) OVER (Partition by dea.location order by dea.location,
dea.date) as TotalPeopleVaccinated
FROM PortfolioProject.dbo.CovidDeaths$ dea
PortfolioProject.dbo.CovidVaccinations$ vac
ON dea.location = vac.location
AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
SELECT *, (TotalPeopleVaccinated/population)*100
FROM #PercentPopulationVaccinated
--Create Views to store data for data visualizations
CREATE VIEW
VaccinatedPopulation AS
SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CAST(vac.new_vaccinations as bigint)) OVER (Partition by dea.location order by dea.location,
dea.date) AS TotalPeopleVaccinated
FROM PortfolioProject.dbo.CovidDeaths$ dea
JOIN PortfolioProject.dbo.CovidVaccinations$ vac
ON dea.location = vac.location
AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
SELECT *
FROM VaccinatedPopulation
CREATE VIEW
DeathsvsCases AS
SELECT date, SUM(new_cases) as global_cases, SUM(cast(new_deaths as int)) as global_deaths,
(SUM(CAST(new_deaths as int))/SUM(new_cases))*100 as globalDeathPercentage
FROM PortfolioProject.dbo.CovidDeaths$
WHERE continent IS NOT NULL
GROUP BY date
SELECT *
FROM DeathsvsCases