COVID-19 Data Exploration

The Data

Select data that we are going to be started with

SELECT continent, location, date, population, total_cases, new_cases, total_deathsFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLORDER BY location, date

Total Cases vs Total Deaths

SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS DeathPercentageFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLORDER BY location, date
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS DeathPercentageFROM PortfolioProject..CovidDeathsWHERE location = 'Indonesia'AND continent IS NOT NULLORDER BY location, date

Total Cases vs Population

SELECT location, date, population, total_cases, (total_cases/population)*100 AS PercentPopulationInfectedFROM PortfolioProject..CovidDeathsWHERE location = 'Indonesia'AND continent IS NOT NULLORDER BY location, date

Countries with Highest Infection Rate compared to Population

SELECT location, population, MAX(total_cases) AS HighestInfectionCount, MAX((total_cases/population))*100 AS PercentPopulationInfectedFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLGROUP BY location,populationORDER BY PercentPopulationInfected DESC

Countries with Highest Death Count

SELECT location, MAX(CAST(total_deaths AS INT)) AS TotalDeathCountFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLGROUP BY locationORDER BY TotalDeathCount DESC

Breaking Things Down by Continent

SELECT continent, MAX(CAST(total_deaths AS INT)) AS TotalDeathCountFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLGROUP BY continentORDER 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 DeathPercentageFROM PortfolioProject..CovidDeathsWHERE continent IS NOT NULLORDER BY total_cases, total_deaths

Total Population vs Vaccinations

SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, SUM(CONVERT(INT, v.new_vaccinations)) OVER (PARTITION BY d.location ORDER BY d.location, d.date) AS RollingPeopleVaccinatedFROM PortfolioProject..CovidDeaths AS dJOIN PortfolioProject..CovidVaccinations AS vON d.location = v.locationAND d.date = v.dateWHERE d.continent IS NOT NULLORDER BY d.location, d.date
WITH PopVsVac (continent, location, date, population, new_vaccinations, RollingPeopleVaccinated)AS(SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, SUM(CONVERT(INT, v.new_vaccinations)) OVER (PARTITION BY d.location ORDER BY d.location, d.date) AS RollingPeopleVaccinatedFROM PortfolioProject..CovidDeaths AS dJOIN PortfolioProject..CovidVaccinations AS vON d.location = v.locationAND d.date = v.dateWHERE d.continent IS NOT NULL)
SELECT *, (RollingPeopleVaccinated/population)*100FROM PopVsVac
DROP TABLE IF EXISTS #PercentPopulationVaccinatedCREATE TABLE #PercentPopulationVaccinated(continent nvarchar(255),location nvarchar(255),date datetime,population numeric,new_vaccinations numeric,RollingPeopleVaccinated numeric)INSERT INTO #PercentPopulationVaccinatedSELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, SUM(CONVERT(INT, v.new_vaccinations)) OVER (PARTITION BY d.location ORDER BY d.location, d.date) AS RollingPeopleVaccinatedFROM PortfolioProject..CovidDeaths AS dJOIN PortfolioProject..CovidVaccinations AS vON d.location = v.locationAND d.date = v.dateWHERE d.continent IS NOT NULLSELECT *, (RollingPeopleVaccinated/population)*100FROM #PercentPopulationVaccinated

Creating View to store data for later visualizations

USE PortfolioProjectGOCREATE VIEW PercentPopulationVaccinated ASSELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, SUM(CONVERT(INT, v.new_vaccinations)) OVER (PARTITION BY d.location ORDER BY d.location, d.date) AS RollingPeopleVaccinatedFROM PortfolioProject..CovidDeaths AS dJOIN PortfolioProject..CovidVaccinations AS vON d.location = v.locationAND d.date = v.dateWHERE d.continent IS NOT NULL

Insights

  • In the United States there were 36,888,921 total cases, 622,321 total deaths, and with that 1.687% death percentage.
  • In Indonesia there were 3,871,738 total cases, 118,833 total deaths, and with that 3.069% death percentage.
  • 1.145% of Indonesia’s population was infected by COVID-19.
  • The United States is the country with the highest death count with 622,321 total deaths.
  • North America is the continent with the highest death count with 622,321 total deaths.
  • There are 207,383,209 total cases, 4,362,647 total deaths, and 2.103% death percentage worldwide.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store