The Cost-of-Living SQL Warehouse

The Business Question

What does it actually cost to live in your county?

National cost-of-living averages hide the picture that matters: the county. A household’s biggest line items split into two rhythms — the monthly nut (rent or mortgage, insurance, childcare) and the weekly basket (gas, groceries, the family dinner plate, the occasional restaurant meal). Those two rhythms come from different federal data systems and almost never sit in the same table.

This project puts them in the same database. The county outputs of two tools I built and published — the True Shelter & Family Cost Index and the True Cost Basket Index — are loaded into a normalized SQLite warehouse and joined on county FIPS, so questions that cross the monthly/weekly boundary become one query instead of two spreadsheets.

The Warehouse

Five tables, normalized on FIPS

A Python loader (build_db.py) reads the two published JSON files, builds the reference tables, and enforces foreign keys. Total load: 9,680 rows.

Question 01 · CTEs + Multi-Table Joins

Where does shelter squeeze harder than the grocery run?

Two CTEs aggregate each index to the state level across three-table joins, then a final join computes the gap between the average renter shelter burden and the average weekly-basket income share.

WITH shelter AS (
    SELECT s.usps, s.name AS state_name,
           ROUND(AVG(t.renter_burden_pct), 1) AS avg_renter_burden_pct,
           COUNT(*) AS n_counties
    FROM tsci_costs t
    JOIN counties c ON c.fips = t.fips
    JOIN states   s ON s.state_fips = c.state_fips
    WHERE t.renter_burden_pct IS NOT NULL
    GROUP BY s.usps, s.name
),
basket AS (
    SELECT s.usps,
           ROUND(AVG(b.basket_income_share_pct), 1) AS avg_basket_share_pct
    FROM tcbi_costs b
    JOIN counties c ON c.fips = b.fips
    JOIN states   s ON s.state_fips = c.state_fips
    WHERE b.basket_income_share_pct IS NOT NULL
    GROUP BY s.usps
)
SELECT sh.usps, sh.state_name, sh.avg_renter_burden_pct,
       ba.avg_basket_share_pct,
       ROUND(sh.avg_renter_burden_pct - ba.avg_basket_share_pct, 1)
           AS shelter_minus_basket_gap
FROM shelter sh
JOIN basket  ba ON ba.usps = sh.usps
ORDER BY shelter_minus_basket_gap DESC
LIMIT 10;

Question 02 · Window Aggregate

Where does infant childcare cost more than rent?

A filter finds every county where monthly infant childcare exceeds median gross rent; COUNT(*) OVER () carries the nationwide total on every row of the ranked output.

SELECT c.fips, c.name AS county, s.usps AS state,
       t.childcare_monthly, t.rent,
       ROUND(t.childcare_monthly * 1.0 / t.rent, 2) AS childcare_to_rent_ratio,
       COUNT(*) OVER () AS total_counties_nationwide
FROM tsci_costs t
JOIN counties c ON c.fips = t.fips
JOIN states   s ON s.state_fips = c.state_fips
WHERE t.childcare_monthly > t.rent
ORDER BY childcare_to_rent_ratio DESC
LIMIT 15;

Question 03 · DENSE_RANK + LAG

Which county tops each state’s cost table — and by how much?

DENSE_RANK() orders counties within each state by total renter monthly nut; LAG() over the same partition fetches the runner-up’s figure, turning “most expensive” into “most expensive, by this margin.”

WITH ranked AS (
    SELECT s.usps AS state, c.name AS county, t.renter_nut,
           DENSE_RANK() OVER (PARTITION BY s.usps
                              ORDER BY t.renter_nut DESC) AS rank_in_state,
           LAG(t.renter_nut) OVER (PARTITION BY s.usps
                                   ORDER BY t.renter_nut)  AS runner_up_nut
    FROM tsci_costs t
    JOIN counties c ON c.fips = t.fips
    JOIN states   s ON s.state_fips = c.state_fips
    WHERE t.renter_nut IS NOT NULL
)
SELECT state, county,
       ROUND(renter_nut)                 AS renter_nut_monthly,
       ROUND(runner_up_nut)              AS runner_up_monthly,
       ROUND(renter_nut - runner_up_nut) AS premium_over_runner_up
FROM ranked
WHERE rank_in_state = 1
ORDER BY renter_nut DESC;

Question 04 · NTILE Quartiles

Do weekly baskets climb as fast as monthly shelter?

NTILE(4) sorts every joined county into renter-nut quartiles, then grouped aggregates compare how each cost family scales from the cheapest quarter of America to the most expensive.

WITH joined AS (
    SELECT t.renter_nut, b.basket_weekly, c.median_income
    FROM tsci_costs t
    JOIN tcbi_costs b ON b.fips = t.fips
    JOIN counties   c ON c.fips = t.fips
    WHERE t.renter_nut IS NOT NULL AND b.basket_weekly IS NOT NULL
),
quartiled AS (
    SELECT NTILE(4) OVER (ORDER BY renter_nut) AS nut_quartile,
           renter_nut, basket_weekly, median_income
    FROM joined
)
SELECT nut_quartile, COUNT(*) AS n_counties,
       ROUND(AVG(renter_nut))       AS avg_renter_nut_monthly,
       ROUND(AVG(basket_weekly), 2) AS avg_basket_weekly,
       ROUND(AVG(median_income))    AS avg_median_income
FROM quartiled
GROUP BY nut_quartile
ORDER BY nut_quartile;

Question 05 · GROUP BY + HAVING

Where is childcare-over-rent a statewide pattern?

Aggregating the Question 02 population by state and keeping only states with 40 or more qualifying counties separates a statewide structural problem from a scattered handful of outliers.

SELECT s.usps, s.name AS state_name,
       COUNT(*)                                 AS counties_cc_over_rent,
       ROUND(AVG(t.childcare_monthly - t.rent)) AS avg_monthly_excess,
       ROUND(MAX(t.childcare_monthly - t.rent)) AS max_monthly_excess
FROM tsci_costs t
JOIN counties c ON c.fips = t.fips
JOIN states   s ON s.state_fips = c.state_fips
WHERE t.childcare_monthly > t.rent
GROUP BY s.usps, s.name
HAVING COUNT(*) >= 40
ORDER BY counties_cc_over_rent DESC;

Question 06 · Correlated Subquery

Which counties most out-spend their own state’s basket?

A correlated subquery recomputes the state’s average weekly basket for each outer county row, surfacing the counties that are priced like a different state than the one they sit in.

SELECT c.name AS county, s.usps AS state, b.basket_weekly,
       (SELECT ROUND(AVG(b2.basket_weekly), 2)
        FROM tcbi_costs b2
        JOIN counties c2 ON c2.fips = b2.fips
        WHERE c2.state_fips = c.state_fips) AS state_avg_basket,
       ROUND(100.0 * b.basket_weekly /
             (SELECT AVG(b3.basket_weekly)
              FROM tcbi_costs b3
              JOIN counties c3 ON c3.fips = b3.fips
              WHERE c3.state_fips = c.state_fips) - 100, 1)
                                            AS pct_above_state_avg
FROM tcbi_costs b
JOIN counties c ON c.fips = b.fips
JOIN states   s ON s.state_fips = c.state_fips
WHERE b.basket_weekly IS NOT NULL
ORDER BY pct_above_state_avg DESC
LIMIT 10;

Methodology & Limits

How it was built, and where to be careful