COVID: Cases, Lockdowns, Vaccines and Death

Data Exploration; What country was proactive in implementing COVID mitigation measures? I explored key COVID metrics to extract insights that reveal this and more.

As humans, we often look to the past to prevent future disaster. The COVID-19 pandemic was a time of lockdowns, uncertainties and as with every pandemic, death. Though unable to name each loss, we can honor them collectively through data.

Using SQL, I explored the COVID-19 global dataset from 1st January, 2020 to 30th April, 2021; the climax of the pandemic. My goal was to gain insight on key metrics and extract data on same for later visualizations. I looked at total cases and deaths, along with the effects of vaccinations and lockdowns during this period. This helped me identify what country was most proactive in implementing these COVID mitigation measures during the time period. Let’s get into it!

I started off with examining the dataset in Excel to get an overview of its structure and cleaning needs before querying the data. From this I was able to separate the COVID dataset into two tables, for death and vaccination data. I also downloaded a third dataset for lockdown data from Kaggle.

Cleaning involved checking for duplicates and standardizing column names & data formats after which the three datasets were imported into MSSQL. The exploration was done by country then by continent to get data at both levels of aggregation. Follow along on my github to see the queries used to extract each table.

First metric explored was the Infection vs Death rates by country. How many people died out of the total cases per day in each country? The query used provided running totals of cases and deaths, as well as, the percentage of people that died out of the total infected for each day.

I further aggregated the running totals to output the total for the time period (instead of daily). So we have a table showing the total cases and deaths between 1st January, 2020 and 30th April, 2021 for each country, as well as the percentage of deaths from COVID-19 when compared to the country’s population at the time.

Next I looked at pre and post lockdown numbers. I subset the data for total cases and deaths to see the daily and total cases before the lockdown date in each country that implemented a lockdown. This is where the lockdown dataset came into play as it was joined to the CovidDeaths table to subset by each country’s lockdown date. This helps us extract data that shows the numbers before or after lockdown to identify its impact, if any.

We could further subset this to see what countries were proactive and initiated lockdowns before any cases or deaths occurred. Only two locations fit the bill; Albania and Hong Kong.

Now we move to Total Cases vs Population. What percentage of the population was infected daily? The query returned the running total of cases per day and what percentage of the population that is. For example, the subset below shows that by 15th November, 2020 about 2% of the UK population had been infected with COVID.

To find the countries with the highest infection rates, I queried the data to return the maximum number of cases for each country and what percentage of its population this is. Andorra had the highest infection rate out of all countries in the explored time period. The top 10 countries with the highest rates are subset below, with the United States taking 9th place by 30th April, 2021.

Now we’ve seen the countries with the highest infection rates, what countries had the highest number of deaths from COVID-19 by 30th April, 2021? Sadly, the US leads with 576,232 lives lost to the pandemic by this time. The Top 5 countries with highest death counts are below.

Let’s break things down by continent. I looked at what continent had the highest infection count at the time. In order words, the continent worst hit by COVID-19. Europe, Asia and North America were the continents with the highest number of recorded cases.

And for highest number of deaths by continent? Europe once again had the highest mortality. But here, North and South America were second and third respectively while Asia followed.

I also had a look at global numbers to see the total cases, total deaths and the percentage of the global population that had been lost to COVID-19 in the time period. By 30th April, 2021, about 2% of the world’s population that had been infected with COVID died.

Then came the vaccines! Let’s look at how and when vaccines were administered per country. I queried the CovidVaccinations table for new vaccinations and outputted the running totals of vaccinations to see the total number of people per day who had received at least one shot of the COVID vaccine. The running total adds the number of the previous day to the next day, so we have the entire number of vaccinated people in a country by that day. Austria started administering vaccines by 28th December, 2020 with about 4,281 people vaccinated in the country before the New Year.

For each country, what percentage of the population received at least one COVID vaccination by 30th April, 2021? This was answered with a CTE (Common Table Expression) that returned the total vaccinated population and what percent of the country’s population this was. By 30th April, about 12% of the Albanian population had received a COVID vaccine.

Let’s give Albania her flowers! This country didn’t only implement an early lockdown, they were also proactive in vaccine administration. Only 4.5% of Albania’s population was infected, but 347,702 people representing 12% of the population were vaccinated by 30th April, 2021. This may have resulted in the 1.8% death rate for the country. It appears that the proactive measures taken by Albania mitigated the impact of COVID on the country. This OECD article corroborates my findings on the country’s proactive response.

So now we have explored all the key COVID metrics for each country, we can join the outputs to create a table of metrics like the one for Albania above and save the table as a view for later queries or export for visualization.

Thank you for getting here! I hope you found this exploration insightful. Remember to check out my github for code and view my portfolio for more data stories.