Covid SQL-Tableau Dashboard
Covid Dashboard
About this project
Description
The data used in this project is from 2020 to August 2023. The data includes information by country, continent, date, number of people infected, deaths, people vaccinated, etc.
This is a project made with SQL and Tableau, and it mainly focuses on data processing, data analysis and data visualization.
This project includes the use of BASIC concepts like ORDER BY, GROUP BY, CASE - WHEN - THEN - ELSE, SUM, TRY_CONVERT, etc. Also, it includes some other more advance concepts like the use of the JOIN function in both SQL and Tableau, and Common Table Expressions (CTE).
Questions for the Analysis
Percentage of people infected by countries.
Continent with highest count of deaths.
Relation between the vaccines and the number of deaths.
Infection worldwide.
Total numbers of deaths, infection cases and death percentage in the world.
SQL Queries
The queries were done in Microsoft SQL Server.
First Query
- The first query is very basic, it gives the global numbers:
--1 Total world numbers
SELECT
SUM(CASE WHEN TRY_CAST(new_cases AS int) IS NOT NULL THEN TRY_CAST(new_cases AS int) ELSE 0 END) AS Total_Cases,
SUM(CASE WHEN TRY_CAST(new_deaths AS int) IS NOT NULL THEN TRY_CAST(new_deaths AS int) ELSE 0 END) AS Total_Deaths,
CASE
WHEN SUM(CASE WHEN TRY_CAST(new_deaths AS int) IS NOT NULL THEN TRY_CAST(new_deaths AS int) ELSE 0 END) > 0
AND SUM(CASE WHEN TRY_CAST(new_cases AS int) IS NOT NULL THEN TRY_CAST(new_cases AS int) ELSE 0 END) > 0
THEN SUM(CASE WHEN TRY_CAST(new_deaths AS int) IS NOT NULL THEN TRY_CAST(new_deaths AS int) ELSE 0 END) * 100.0 /
SUM(CASE WHEN TRY_CAST(new_cases AS int) IS NOT NULL THEN TRY_CAST(new_cases AS int) ELSE 0 END)
ELSE 0
END AS DeathPercentage
FROM
PortfolioProject..CovidDeaths_actual
WHERE
continent IS NOT NULL
AND continent <> ''
ORDER BY
1,2
Result First Query
Second Query
The second query gives information per continents:
-- 2 Continents with highest death count
SELECT
location,
MAX(
CASE
WHEN TRY_CONVERT(float, total_deaths) > 0
THEN CAST(total_deaths AS int)
ELSE 0
END) AS TotalDeathCount
FROM
PortfolioProject..CovidDeaths_actual
WHERE
continent IS NULL OR continent = ''
AND location not in ('World', 'European Union', 'International', 'High income', 'Upper middle income', 'Lower middle income', 'Low income')
GROUP BY
location
ORDER BY
TotalDeathCount DESC
Result Second Query
Third Query
The third query gives the percentage of infected population per country:
-- 3 Countries with Highest Infection Rate compared to Population
SELECT
location,
population,
MAX(total_cases) AS HighestInfectionCount,
MAX(
CASE
WHEN TRY_CONVERT(float, total_cases) > 0 AND TRY_CONVERT(float, population) > 0
THEN TRY_CONVERT(float, total_cases) * 100.0 / TRY_CONVERT(float, population)
ELSE 0
END
) AS PercentPopulationInfected
FROM
PortfolioProject..CovidDeaths_actual
Where
continent is not null
AND continent <> ''
GROUP BY
location, population
ORDER BY
PercentPopulationInfected DESC
Partial Result Third Query
Fourth Query
The fourth query gives information about the percentage of the total infected population in the world:
-- 4 Percentage of infected Population in the world
SELECT
location,
population,
date,
MAX(total_cases) AS HighestInfectionCount,
MAX(
CASE
WHEN TRY_CONVERT(float, total_cases) > 0 AND TRY_CONVERT(float, population) > 0
THEN TRY_CONVERT(float, total_cases) * 100.0 / TRY_CONVERT(float, population)
ELSE 0
END
) AS PercentPopulationInfected
FROM
PortfolioProject..CovidDeaths_actual
WHERE
Location = 'World'
GROUP BY
location, population, date
ORDER BY
PercentPopulationInfected DESC
Partial Result Fourth Query
Fifth Query
The fifth query makes use of a Common Table Expression or CTE, and it gives information about deaths and vaccines in the world:
-- 5 Total population vaccinated and total deaths in the world
WITH PopvsVac (Continent, Location, Date, Population, People_Vaccinated, Total_Deaths) AS (
SELECT
dea.continent,
dea.location,
dea.date,
dea.population,
vac.people_vaccinated,
dea.total_deaths
FROM
PortfolioProject..CovidDeaths_actual AS dea
JOIN PortfolioProject..CovidVaccination_actual AS vac
ON dea.date = vac.date
AND dea.location = vac.location)
SELECT *,
CASE
WHEN TRY_CONVERT(float, People_Vaccinated) > 0 AND TRY_CONVERT(float, Population) > 0
THEN TRY_CONVERT(float, People_Vaccinated) * 100.0 / TRY_CONVERT(float, Population)
ELSE 0
END AS PeopleVaccinatedPercent,
CASE
WHEN TRY_CONVERT(float, Total_Deaths) > 0 AND TRY_CONVERT(float, Population) > 0
THEN TRY_CONVERT(float, Total_Deaths) * 100.0 / TRY_CONVERT(float, Population)
ELSE 0
END AS DeathPercent
FROM
PopvsVac
WHERE
Location = 'World'
ORDER BY
Date
Partial Result Fifth Query
Sixth Query
Finally, one extra query was made in order to get information on new deaths instead of the total account of deaths, so that way it could be compared with the percentage of vaccinated people:
SELECT
dea.location,
dea.date,
dea.population,
vac.new_vaccinations,
vac.people_vaccinated,
dea.new_deaths
FROM
PortfolioProject..CovidDeaths_actual AS dea
JOIN PortfolioProject..CovidVaccination_actual AS vac
ON dea.date = vac.date
AND dea.location = vac.location
WHERE
dea.location = 'World'
ORDER BY
Date
Partial Result Sixth Query
Later, Table 5 and Table 6 were joined in Tableau.
Conclusions and Insights
In the upper right corner, the map shows the percentage of people infected by country . The darker the color the higher the percentage of people infected.
In the lower right corner, the percentage of people vaccinated and deaths are shown as time lines, which allows us to observe that as the number of vaccinated people increases, the number of deaths decreases. As of August 23, 2023, around 70% of the world population has at least one vaccine and the number of deaths is around 1,000 for this month.
In the lower left corner, a time line is presented with the percentage of people infected in the world since the pandemic began, as well as a prediction for 2025 that said percentage will remain in the same range.
In the center of the left half of the panel, the total death count by continent is shown, where clearly America is the most affected continent with almost 3 million deaths if we put North America and South America together.
Finally, II think the graph on the bottom right requires special attention, it makes us realize the importance of vaccines in reducing deaths, and perhaps also makes us think "what would have happened if they had not developed the vaccines on time? ". Unfortunately, we also realize that not all people have received the vaccine up to these days, either by their own decision or by external causes.
Source of the data: https://ourworldindata.org/covid-deaths
SQL code: https://github.com/CaralGC
More projects by Carlos
