Jul 17, 2024
Percentage of Deaths: Calculate the percentage of deaths among total cases.
SELECT
location,
date,
total_cases,
total_deaths,
(total_deaths / total_cases * 100) AS death_percentage
FROM covid_deaths
WHERE continent IS NOT NULL
ORDER BY location, date
Population Percentage Infected: Calculate the percentage of population infected by COVID-19.
SELECT
location,
date,
total_cases,
population,
(total_cases / population * 100) AS percent_population_infected
FROM covid_deaths
WHERE continent IS NOT NULL
ORDER BY location, date
Highest Infection Rate: Identify countries with the highest infection rates as a percentage of their population.
SELECT
location,
MAX(total_cases) AS highest_infection_count,
population,
(MAX(total_cases) / population * 100) AS percent_population_infected
FROM covid_deaths
WHERE continent IS NOT NULL
GROUP BY location, population
ORDER BY percent_population_infected DESC
Highest Death Count per Population: Identify countries with the highest death counts as a percentage of their population.
SELECT
location,
MAX(CAST(total_deaths AS int)) AS total_death_count
FROM covid_deaths
WHERE continent IS NOT NULL
GROUP BY location
ORDER BY total_death_count DESC
Breaking Things Down by Continent: Aggregate data to show continental stats.
SELECT
continent,
SUM(total_cases) AS total_cases,
SUM(CAST(total_deaths AS int)) AS total_deaths
FROM covid_deaths
WHERE location IN ('World',...)
GROUP BY continent
WITH pop_vs_vacc AS (
SELECT
continent,
location,
date,
population,
new_vaccinations,
SUM(new_vaccinations) OVER (PARTITION BY location ORDER BY date) AS rolling_people_vaccinated
FROM covid_deaths
WHERE continent IS NOT NULL
AND new_vaccinations IS NOT NULL
)
SELECT *,
(rolling_people_vaccinated / population * 100) AS percent_population_vaccinated
FROM pop_vs_vacc
CREATE VIEW percent_population_vaccinated AS
SELECT
continent,
location,
date,
population,
new_vaccinations,
SUM(new_vaccinations) OVER (PARTITION BY location ORDER BY date) AS rolling_people_vaccinated,
(SUM(new_vaccinations) OVER (PARTITION BY location ORDER BY date) / population * 100) AS percent_population_vaccinated
FROM covid_deaths
WHERE continent IS NOT NULL
AND new_vaccinations IS NOT NULL;
``
Future Steps