US Household Income
A complete SQL cleaning and exploratory workflow for United States household-income and geographic data.
The question
Turn two inconsistent public datasets into a reliable foundation for comparing household income across states, cities, places, and area types.
The approach
I profiled both tables, removed duplicates, standardized state and area labels, filled missing places, checked land and water fields, joined the datasets, and built income comparisons at several geographic levels.
The outcome
The final queries support state rankings, land and water comparisons, mean-versus-median income analysis, and targeted city or area-type drilldowns.
Analysis questions
What the work needed to answer.
- 01
Are identifiers, state names, places, and area labels consistent?
- 02
Which states have the highest and lowest land and water totals?
- 03
How do mean and median household income differ by state and area type?
- 04
Which city-level patterns deserve a closer drilldown?
Method
From raw data to a useful answer.
Profile and rename
Checked row counts and schemas, then aligned the ID field for a reliable join.
Remove duplicates
Used ROW_NUMBER to identify repeated IDs and retain one valid record.
Standardize geography
Corrected Georgia and Alabama labels, filled missing places, and consolidated inconsistent borough naming.
Explore income
Joined geography with statistics and compared mean and median income by state, city, place, and area type.
Results
What the analysis revealed.
Small inconsistencies matter
Misspelled states, missing place names, and inconsistent area labels would have fragmented group-level results.
Window functions simplify deduplication
Partitioning by ID made repeated records explicit and safe to remove.
Mean and median tell different stories
Viewing both measures prevents a small number of high-income areas from defining the typical household.
The joined view supports drilldown
Once cleaned, the same model can move from national comparisons to states, area types, and individual cities.