Three analyses in SQL and Excel — cleaning messy real-world data, then asking questions of it.
US Household Income · World Life Expectancy · US Debt Tracker
📊 Dashboards: Tableau Public
How does household income vary across the United States, and does inequality follow income level, area type, or geography?
Findings
- Average US household income is just under $65,000/year, with high dispersion (CV 0.73) and a mild right skew (Pearson 0.69).
- Inequality is highest where income is lowest. Average income and relative dispersion move in opposite directions — poorer areas are more unequal among themselves than wealthy ones are. This is the strongest relationship in the data, and it holds at both area and regional level.
- Geography is the clearest divide. Northeast $75,000 · West $70,000 · Midwest $59,500 · South $57,700 — a $17,000 spread, with 5 of the 6 highest-earning areas nationally in the Northeast.
- Area size predicts nothing. Tested two separate ways — by area type and by size quartile — and reported as a negative result rather than dropped.
Data-quality note that changes the answer: median = 300000 occurs far too often and at a
suspiciously round figure — a cap, not a measurement. It is excluded from every calculation but
deliberately not deleted from the source, and its uneven concentration across states is
measured first so any state-level figure can be read with that in mind.
SQL used: a reusable view and a dynamic stored procedure, NTILE quartiles, RANK and
LEAD window functions, CTEs, conditional aggregation, and a CASE expression deriving Census
Bureau regions from latitude/longitude bounds.
A country-by-year panel: which countries gained the most, how the global average moved, and whether GDP, development status and BMI relate to life expectancy.
The interesting part is the cleaning. Missing life-expectancy values are not dropped, and
not filled with a global average. They are filled by self-joining each row to the same
country's previous and next year and interpolating the midpoint — life expectancy moves
smoothly, so neighbouring years are the best estimate available, and the fill never crosses a
country boundary. Missing Status values are recovered the same way, from the country's own
other rows.
SQL used: ROW_NUMBER deduplication on a composite key, multi-table self-joins for
interpolation, conditional aggregation comparing GDP bands side by side in one row, and a
running total with SUM() OVER (PARTITION BY ... ORDER BY ...).
30 years of daily US public debt — 7,495 records, April 1993 to February 2023 — built into an Excel model with pivot tables and charts.
Findings
- Debt grows fastest in crises. The three sharpest yearly increases are 2009 (+18.0%), 2020 (+14.8%) and 2010 (+14.7%) — the financial crisis and COVID.
- The two components behave completely differently. In 2009, debt held by the public grew 28.4% while intragovernmental holdings grew just 4.1%; in 2020, 19.5% against 1.6%. Crisis borrowing goes to the public market, not internal accounts — so total debt alone hides where the pressure actually lands.
- Monthly averages show a consistent within-year cycle, dipping through spring and building again toward December.
├── us-household-income/
│ ├── 00_views_and_procedures.sql # reusable view + dynamic stored procedure
│ ├── 01_data_cleaning.sql
│ └── 02_exploratory_analysis.sql # findings written up inline
├── world-life-expectancy/
│ ├── 01_data_cleaning.sql
│ └── 02_exploratory_analysis.sql
└── us-debt-tracker/
└── us_debt_tracker.xlsx # raw data, cleaned data, pivots, charts
Written for MySQL 8.0+ — window functions and CTEs are used throughout. Run the numbered files in order.
Part of a data-analytics portfolio. See also the machine learning projects on my profile.