SQL Analysis — COVID-19 Data Analysis

Setup

Load covid_countries_clean.csv into a table covid. Key columns: location, continent, date, new_cases, new_deaths, total_cases, total_deaths, total_cases_per_million, total_deaths_per_million, population, new_cases_7d, people_vaccinated.

---

Q1 — Highest Cases Per Capita (Fair Comparison)

-- Latest cumulative cases per million, per country
WITH latest AS (
    SELECT location, MAX(date) AS max_date
    FROM covid
    WHERE continent IS NOT NULL
    GROUP BY location
)
SELECT
    c.location,
    ROUND(c.total_cases_per_million, 0)     AS cases_per_million,
    ROUND(c.total_deaths_per_million, 0)    AS deaths_per_million,
    ROUND(c.population / 1000000, 1)        AS population_millions
FROM covid c
JOIN latest l ON c.location = l.location AND c.date = l.max_date
ORDER BY cases_per_million DESC
LIMIT 15;

Why per-capita changes everything

By raw total cases, large countries dominate. By cases per million, small countries with intense outbreaks rise to the top. This is the fair comparison.

---

Q2 — Case Fatality Rate by Country

WITH latest AS (
    SELECT location, MAX(date) AS max_date
    FROM covid WHERE continent IS NOT NULL
    GROUP BY location
)
SELECT
    c.location,
    c.total_cases,
    c.total_deaths,
    ROUND(c.total_deaths * 100.0 / NULLIF(c.total_cases, 0), 2) AS cfr_pct
FROM covid c
JOIN latest l ON c.location = l.location AND c.date = l.max_date
WHERE c.total_cases > 100000   -- meaningful sample
ORDER BY cfr_pct DESC
LIMIT 15;

CFR is not the true death rate

Case Fatality Rate = deaths / confirmed cases. Countries with limited testing detect fewer mild cases, inflating their CFR. CFR reflects testing capacity as much as disease severity — always caveat this.

---

Q3 — Global Monthly Case Totals (Sum Daily, Not Cumulative)

SELECT
    year_month,
    ROUND(SUM(new_cases), 0)        AS monthly_new_cases,
    ROUND(SUM(new_deaths), 0)       AS monthly_new_deaths
FROM covid
WHERE location = 'World'
GROUP BY year_month
ORDER BY year_month;

Sum new_cases, never total_cases

total_cases is cumulative — summing it across days is meaningless. To get a monthly total, sum the daily new_cases.

---

Q4 — Detect Each Country's Peak Day

WITH ranked AS (
    SELECT
        location,
        date,
        new_cases_7d,
        ROW_NUMBER() OVER (PARTITION BY location ORDER BY new_cases_7d DESC) AS rn
    FROM covid
    WHERE continent IS NOT NULL
)
SELECT
    location,
    date            AS peak_date,
    ROUND(new_cases_7d, 0) AS peak_daily_cases_7d_avg
FROM ranked
WHERE rn = 1
ORDER BY peak_daily_cases_7d_avg DESC
LIMIT 15;

Uses the 7-day average (not raw daily) to find the true peak, ignoring single-day reporting spikes.

---

Q5 — Vaccination Progress

WITH latest AS (
    SELECT location, MAX(date) AS max_date
    FROM covid WHERE continent IS NOT NULL AND people_vaccinated IS NOT NULL
    GROUP BY location
)
SELECT
    c.location,
    ROUND(c.people_vaccinated / c.population * 100, 1) AS pct_vaccinated_1dose,
    ROUND(c.population / 1000000, 1)                   AS pop_millions
FROM covid c
JOIN latest l ON c.location = l.location AND c.date = l.max_date
WHERE c.population > 1000000
ORDER BY pct_vaccinated_1dose DESC
LIMIT 15;

---

Q6 — Wave Identification (Month-over-Month Change)

WITH monthly AS (
    SELECT
        location,
        year_month,
        SUM(new_cases) AS monthly_cases
    FROM covid
    WHERE location IN ('United States', 'United Kingdom', 'India')
    GROUP BY location, year_month
)
SELECT
    location,
    year_month,
    ROUND(monthly_cases, 0)                                     AS cases,
    ROUND(LAG(monthly_cases) OVER (PARTITION BY location ORDER BY year_month), 0) AS prev_month,
    CASE
        WHEN monthly_cases > LAG(monthly_cases) OVER (PARTITION BY location ORDER BY year_month) * 1.5
        THEN 'WAVE RISING'
        WHEN monthly_cases < LAG(monthly_cases) OVER (PARTITION BY location ORDER BY year_month) * 0.5
        THEN 'declining'
        ELSE 'stable'
    END                                                         AS phase
FROM monthly
ORDER BY location, year_month;

---

Q7 — Continent-Level Summary

WITH latest AS (
    SELECT location, MAX(date) AS max_date
    FROM covid WHERE continent IS NOT NULL
    GROUP BY location
)
SELECT
    c.continent,
    COUNT(DISTINCT c.location)              AS countries,
    ROUND(SUM(c.total_cases) / 1000000, 1)  AS total_cases_millions,
    ROUND(SUM(c.total_deaths) / 1000, 0)    AS total_deaths_thousands,
    ROUND(SUM(c.total_deaths) * 100.0 / NULLIF(SUM(c.total_cases), 0), 2) AS cfr_pct
FROM covid c
JOIN latest l ON c.location = l.location AND c.date = l.max_date
GROUP BY c.continent
ORDER BY total_cases_millions DESC;

---

← Data Cleaning · Next: Dashboard Design →