Covid Deaths & Vaccinations Analysis

This project, is based on Alex Freberg’s Portoflio Project Series where we do basic data exploration in SQL.

Code

--Select Data that we are going to be using 
SELECT 
    location,
    date,
    total_cases,
    new_cases,
    total_deaths,
    population
FROM 
    Covid..CovidDeaths
ORDER BY
    1,2


-- Looking at Total Cases v. Total Deaths (daily death rate) in the United States
SELECT 
    location,
    date,
    total_cases,
    total_deaths,
    (total_deaths / total_cases) * 100 AS death_percentage
FROM 
    Covid..CovidDeaths
Where location like '%state%'
ORDER BY
    1,2


-- Looking at Total Cases v. Population 
-- Percent of population were infected
SELECT 
    location,
    date,
    total_cases,
    population,
    (total_cases / population) * 100 AS infected_percentage
FROM 
    Covid..CovidDeaths
Where location like '%state%'
ORDER BY
    1,2


-- Looking at countries with highest infection rate compared to population 
SELECT 
    location,
    MAX(total_cases) AS highest_infection_count,
    population,
    (MAX(total_cases) / population) * 100 AS infected_percentage
FROM 
    Covid..CovidDeaths
GROUP BY 
    location,
    population
ORDER BY
    infected_percentage DESC


-- Showing countries with the highest death count per population 
SELECT 
    location,
    MAX(total_deaths) AS total_death_count
FROM 
    Covid..CovidDeaths
WHERE continent IS NOT NULL
GROUP BY 
    location
ORDER BY
    total_death_count DESC


-- Showing the contients with the highest death counts 
SELECT 
    location,
    MAX(total_deaths) AS total_death_count
FROM 
    Covid..CovidDeaths
WHERE continent IS NULL
GROUP BY 
    location
ORDER BY
    total_death_count DESC


-- Global Numbers
SELECT 
    date,
    SUM(new_cases) AS new_case_count,
    SUM(new_deaths) AS new_death_cout,
    (SUM(new_deaths) / SUM(new_cases)) * 100 AS global_death_percentage
FROM 
    Covid..CovidDeaths
WHERE continent IS NOT NULL
GROUP BY date
ORDER BY
    1,2


-- Total Cases v. Total Deaths (Globally)
SELECT 
    SUM(new_cases) AS new_case_count,
    SUM(new_deaths) AS new_death_cout,
    (SUM(new_deaths) / SUM(new_cases)) * 100 AS global_death_percentage
FROM 
    Covid..CovidDeaths
WHERE continent IS NOT NULL


-- Looking at Total Population v. Vaccinations
WITH PopvVac (Continent, Location, Date, Population, New_Vaccination, Rolling_Total_Vaccination)
AS
(
SELECT 
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations,
    SUM(vac.new_vaccinations) 
        OVER (PARTITION BY dea.location 
            ORDER BY dea.location, dea.date) AS rolling_total_vaccination    
FROM
    Covid..CovidDeaths dea 
Join Covid..CovidVaccinations vac
    ON dea.location = vac.location 
    and dea.date = vac.date
WHERE dea.continent IS NOT NULL
)

SELECT 
    *,
    (Rolling_Total_Vaccination / Population) * 100 AS Rolling_Vaccination_Percentage
FROM PopvVac


-- Creating View to store data for later visualization 
CREATE VIEW PopvVac 
AS
    SELECT 
        dea.continent,
        dea.location,
        dea.date,
        dea.population,
        vac.new_vaccinations,
        SUM(vac.new_vaccinations) 
            OVER (PARTITION BY dea.location 
                ORDER BY dea.location, dea.date) AS rolling_total_vaccination    
    FROM
        Covid..CovidDeaths dea 
        Join Covid..CovidVaccinations vac
            ON dea.location = vac.location 
            and dea.date = vac.date
    WHERE dea.continent IS NOT NULL