Transformations create new calculated columns or categorize data based on conditions.
Transforming 1: Classify towns by size category
Scenario: Create size tiers for a stratified analysis. CASE WHEN acts like an IF-THEN statement. Show town, state, population_2020_census, and a size_category labeling each place ‘Large City’ (100,000 or more), ‘Medium City’ (25,000 or more), ‘Small City’ (5,000 or more), or ‘Town’ otherwise.
✓ Completed
-- Write your SQL query here
SELECT town,
state,
population_2020_census,
CASE
WHEN population_2020_census >= 100000 THEN 'Large City'
WHEN population_2020_census >= 25000 THEN 'Medium City'
WHEN population_2020_census >= 5000 THEN 'Small City'
ELSE 'Town'
END AS size_category
FROM pnw_towns
ORDER BY population_2020_census DESC;
Transforming 2: Calculate population change from 2010 to 2020
Scenario: Identify growing and shrinking towns. Show town, state, the census figures as pop_2010 and pop_2020, the difference as pop_change, and the percentage change (1 decimal) as pct_change.
✓ Completed
-- Write your SQL query here
SELECT town,
state,
population_2010_census AS pop_2010,
population_2020_census AS pop_2020,
population_2020_census - population_2010_census AS pop_change,
ROUND((population_2020_census - population_2010_census) * 100.0 / population_2010_census, 1) AS pct_change
FROM pnw_towns
WHERE population_2010_census > 0
ORDER BY pct_change DESC;
Transforming 3: Identify fastest-growing and declining towns
Scenario: Combine CASE WHEN with calculations to flag growth status. Show town, state, pop_2010, pop_2020, and a growth_status labeled ‘Rapid Growth (>20%)’ for growth above 20%, ‘Growing’, ‘Stable’, ‘Declining’ for declines up to 20%, or ‘Rapid Decline (>20%)’.
✓ Completed
-- Write your SQL query here
SELECT town,
state,
population_2010_census AS pop_2010,
population_2020_census AS pop_2020,
CASE
WHEN population_2020_census > population_2010_census * 1.20 THEN 'Rapid Growth (>20%)'
WHEN population_2020_census > population_2010_census THEN 'Growing'
WHEN population_2020_census = population_2010_census THEN 'Stable'
WHEN population_2020_census > population_2010_census * 0.80 THEN 'Declining'
ELSE 'Rapid Decline (>20%)'
END AS growth_status
FROM pnw_towns
WHERE population_2010_census > 0
ORDER BY (population_2020_census - population_2010_census) * 1.0 / population_2010_census DESC;
Transforming 4: Calculate and categorize population density
Scenario: Classify towns as urban, suburban, or rural based on density. Show town, state, population_2020_census, land_area_sq_mi, the people per square mile (rounded to a whole number) as density, and a density_class of ‘Urban’ (over 5,000), ‘Suburban’ (over 1,000), or ‘Rural’.
✓ Completed
-- Write your SQL query here
SELECT town,
state,
population_2020_census,
land_area_sq_mi,
ROUND(population_2020_census / land_area_sq_mi, 0) AS density,
CASE
WHEN population_2020_census / land_area_sq_mi > 5000 THEN 'Urban'
WHEN population_2020_census / land_area_sq_mi > 1000 THEN 'Suburban'
ELSE 'Rural'
END AS density_class
FROM pnw_towns
WHERE land_area_sq_mi > 0
ORDER BY density DESC;
Transforming 5: Flag counties by era of establishment
Scenario: Categorize counties by historical period. Show county, state, year_established, and a historical_era of ‘Pioneer Era’ (before 1850), ‘Settlement Era’ (before 1890), ‘Progressive Era’ (before 1920), or ‘Modern Era’.
✓ Completed
-- Write your SQL query here
SELECT county,
state,
year_established,
CASE
WHEN year_established < 1850 THEN 'Pioneer Era'
WHEN year_established < 1890 THEN 'Settlement Era'
WHEN year_established < 1920 THEN 'Progressive Era'
ELSE 'Modern Era'
END AS historical_era
FROM pnw_counties
ORDER BY year_established;