Overview
A SQL-only project in MySQL. I started with a raw global health dataset, found and fixed the problems that would have distorted the results, and then used the cleaned data to answer seven questions about life expectancy, wealth and health.
The questions
- Which countries have the highest and lowest life expectancy?
- Is global life expectancy improving over time?
- How do developed and developing countries compare?
- Which countries improved the most over the period?
- Do wealthier countries live longer?
- Did any countries see a sudden year-on-year change?
- Does average BMI relate to life expectancy?
Dataset
The World Life Expectancy dataset from the AnalystBuilder SQL course: 2,941 rows covering 193 countries from 2007 to 2022. Each row is one country in one year, with life expectancy, GDP, adult mortality, years of schooling, average BMI and a Developed or Developing status.
This is a teaching dataset rather than an official statistics release, so the figures below describe this data and should not be quoted as official national statistics.
Tools
MySQL, using window functions (ROW_NUMBER, LAG), common table expressions, self joins, subqueries, CASE statements and aggregate functions.
Data preparation and method
I checked the raw table for three kinds of problem before running any analysis.
1. Duplicate records
Some country and year combinations appeared more than once, which would double-count those years. I numbered the rows within each country–year pair and deleted every row after the first, which removed 3 duplicates. I then compared the row count against a backup copy of the table to confirm that only those rows had gone.
DELETE FROM worldlifexpectancy
WHERE Row_ID IN (
SELECT Row_ID FROM (
SELECT Row_ID,
ROW_NUMBER() OVER (PARTITION BY CONCAT(Country, Year)
ORDER BY CONCAT(Country, Year)) AS row_num
FROM worldlifexpectancy
) AS numbered
WHERE row_num > 1
);2. Missing status labels
Eight rows had no Developed or Developing label. A country's status doesn't change from year to year in this data, so I used a self join to copy the label from the same country's other rows.
3. Missing life expectancy values
Two rows had no life expectancy figure. Rather than delete them or use an overall average, I estimated each one as the midpoint of the same country's previous and following years, again with a self join.
Analysis
With the data cleaned, I wrote one or more queries for each question: averages by country and year, a comparison by status, the change between each country's first and last year, and year-on-year changes using LAG to spot sudden jumps or drops.
Key findings
Average life expectancy (years)
The global average went from 66.8 years in 2007 to 71.6 years in 2022. There is a clear dip in 2020, which lines up with the COVID-19 pandemic.
Developed countries averaged 79.2 years against 66.8 for developing countries, with less than half the adult mortality rate.
Zimbabwe gained 21 years of life expectancy over the period, the largest increase in the dataset. Japan and Sweden had the highest averages, at 82.5 years.
Countries with higher average GDP tended to have longer life expectancy, but the gain flattened at higher income levels.
Countries with a higher average BMI also had longer life expectancy. Those countries also tended to be wealthier, so GDP is the more likely explanation than BMI itself.
| Measure | Developed | Developing |
|---|---|---|
| Life expectancy (years) | 79.2 | 66.8 |
| Adult mortality | 79.7 | 182.1 |
Why this matters
The same routine — check for duplicates and gaps, record each fix, confirm the row counts, then compare groups — applies to any dataset a business reports from, such as sales or customer records. Cleaning first is what makes the comparisons trustworthy.
Limitations and what I learned
- The analysis is descriptive. The GDP and BMI patterns come from comparing averages, not from a statistical model, so they show associations rather than causes.
- Two life expectancy values are estimates, and status labels were filled on the assumption that a country's status doesn't change.
- The data comes from a course, so the results describe this dataset rather than official statistics.
The main lesson was to treat cleaning as part of the analysis: keeping a backup and checking counts after every change made each fix easy to verify. The BMI result also taught me to look for a third factor before accepting a pattern at face value.