Maricopa County, AZ leads counties that pair low disaster exposure with heavy industrial infrastructure — Ohio and Nevada manufacturing counties follow

WITH disaster_counts AS (
  SELECT county_fips, COUNT(DISTINCT disaster_number) AS major_disasters
  FROM disasters.disaster_declarations
  WHERE declaration_type = 'DR' AND county_fips IS NOT NULL
  GROUP BY county_fips
),
manuf AS (
  SELECT area_fips AS county_fips, SUM(annual_avg_emplvl) AS manuf_emp
  FROM econ.county_qcew
  WHERE industry_code = '31-33' AND own_code = '5' AND "year" = '2024'
  GROUP BY area_fips
),
elec AS (
  SELECT county_fips, SUM(sales_industrial_mwh) AS ind_mwh
  FROM energy.eia_electricity_demand_by_county
  WHERE report_year = 2023
  GROUP BY county_fips
),
pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
)
SELECT
  (SELECT count(*) FROM disaster_counts) AS n_disaster,
  (SELECT count(*) FROM manuf) AS n_manuf,
  (SELECT count(*) FROM elec) AS n_elec,
  (SELECT count(*) FROM pop) AS n_pop;

SELECT COUNT(*) FROM disasters.disaster_declarations WHERE declaration_type = 'DR' AND county_fips IS NOT NULL

WITH pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
)
SELECT * FROM pop LIMIT 5

WITH disaster_counts AS (
  SELECT county_fips, COUNT(DISTINCT disaster_number) AS major_disasters
  FROM disasters.disaster_declarations
  WHERE declaration_type = 'DR' AND county_fips IS NOT NULL
  GROUP BY county_fips
),
manuf AS (
  SELECT area_fips AS county_fips, SUM(annual_avg_emplvl) AS manuf_emp
  FROM econ.county_qcew
  WHERE industry_code = '31-33' AND own_code = '5' AND "year" = '2024'
  GROUP BY area_fips
),
elec AS (
  SELECT county_fips, SUM(sales_industrial_mwh) AS ind_mwh
  FROM energy.eia_electricity_demand_by_county
  WHERE report_year = 2023
  GROUP BY county_fips
),
pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
),
base AS (
  SELECT p.county_fips AS cfips, p.population AS population,
         COALESCE(d.major_disasters, 0) AS major_disasters,
         COALESCE(m.manuf_emp, 0) AS manuf_emp,
         COALESCE(e.ind_mwh, 0) AS ind_mwh
  FROM pop p
  LEFT JOIN disaster_counts d ON p.county_fips = d.county_fips
  LEFT JOIN manuf m ON p.county_fips = m.county_fips
  LEFT JOIN elec e ON p.county_fips = e.county_fips
  WHERE p.population >= 30000
),
scaled AS (
  SELECT cfips, population, major_disasters, manuf_emp, ind_mwh,
    1.0 - (major_disasters - MIN(major_disasters) OVER()) * 1.0 / NULLIF(MAX(major_disasters) OVER() - MIN(major_disasters) OVER(),0) AS disaster_score,
    (manuf_emp - MIN(manuf_emp) OVER()) * 1.0 / NULLIF(MAX(manuf_emp) OVER() - MIN(manuf_emp) OVER(),0) AS manuf_score,
    (ind_mwh - MIN(ind_mwh) OVER()) * 1.0 / NULLIF(MAX(ind_mwh) OVER() - MIN(ind_mwh) OVER(),0) AS elec_score
  FROM base
)
SELECT s.cfips, c.county_name, c.state_fips,
       s.population, s.major_disasters, s.manuf_emp, s.ind_mwh,
       ROUND(disaster_score,3) AS disaster_score,
       ROUND(manuf_score,3) AS manuf_score,
       ROUND(elec_score,3) AS elec_score,
       ROUND(0.5*disaster_score + 0.25*manuf_score + 0.25*elec_score, 4) AS composite_score
FROM scaled s
JOIN geo.counties c ON s.cfips = c.county_fips
WHERE c."year" = '2023'
ORDER BY composite_score DESC
FETCH FIRST 25 ROWS ONLY

WITH disaster_counts AS (
  SELECT county_fips, COUNT(DISTINCT disaster_number) AS major_disasters
  FROM disasters.disaster_declarations
  WHERE declaration_type = 'DR' AND county_fips IS NOT NULL
  GROUP BY county_fips
),
manuf AS (
  SELECT area_fips AS county_fips, SUM(annual_avg_emplvl) AS manuf_emp
  FROM econ.county_qcew
  WHERE industry_code = '31-33' AND own_code = '5' AND "year" = '2024'
  GROUP BY area_fips
),
elec AS (
  SELECT county_fips, SUM(sales_industrial_mwh) AS ind_mwh
  FROM energy.eia_electricity_demand_by_county
  WHERE report_year = 2023
  GROUP BY county_fips
),
pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
),
base AS (
  SELECT p.county_fips AS cfips, p.population AS population,
         COALESCE(d.major_disasters, 0) AS major_disasters,
         COALESCE(m.manuf_emp, 0) AS manuf_emp,
         COALESCE(e.ind_mwh, 0) AS ind_mwh
  FROM pop p
  LEFT JOIN disaster_counts d ON p.county_fips = d.county_fips
  LEFT JOIN manuf m ON p.county_fips = m.county_fips
  LEFT JOIN elec e ON p.county_fips = e.county_fips
  WHERE p.population >= 30000 AND COALESCE(m.manuf_emp,0) >= 5000
),
scaled AS (
  SELECT cfips, population, major_disasters, manuf_emp, ind_mwh,
    1.0 - (major_disasters - MIN(major_disasters) OVER()) * 1.0 / NULLIF(MAX(major_disasters) OVER() - MIN(major_disasters) OVER(),0) AS disaster_score,
    (manuf_emp - MIN(manuf_emp) OVER()) * 1.0 / NULLIF(MAX(manuf_emp) OVER() - MIN(manuf_emp) OVER(),0) AS manuf_score,
    (ind_mwh - MIN(ind_mwh) OVER()) * 1.0 / NULLIF(MAX(ind_mwh) OVER() - MIN(ind_mwh) OVER(),0) AS elec_score
  FROM base
)
SELECT s.cfips, c.county_name, c.state_fips,
       s.population, s.major_disasters, s.manuf_emp, ROUND(s.ind_mwh) AS ind_mwh,
       ROUND(disaster_score,3) AS disaster_score,
       ROUND(manuf_score,3) AS manuf_score,
       ROUND(elec_score,3) AS elec_score,
       ROUND(0.4*disaster_score + 0.4*manuf_score + 0.2*elec_score, 4) AS composite_score
FROM scaled s
JOIN geo.counties c ON s.cfips = c.county_fips
WHERE c."year" = '2023'
ORDER BY composite_score DESC
FETCH FIRST 15 ROWS ONLY

WITH disaster_counts AS (
  SELECT county_fips, COUNT(DISTINCT disaster_number) AS major_disasters
  FROM disasters.disaster_declarations
  WHERE declaration_type = 'DR' AND county_fips IS NOT NULL
  GROUP BY county_fips
),
manuf AS (
  SELECT area_fips AS county_fips, SUM(annual_avg_emplvl) AS manuf_emp
  FROM econ.county_qcew
  WHERE industry_code = '31-33' AND own_code = '5' AND "year" = '2024'
  GROUP BY area_fips
),
elec AS (
  SELECT county_fips, SUM(sales_industrial_mwh) AS ind_mwh
  FROM energy.eia_electricity_demand_by_county
  WHERE report_year = 2023
  GROUP BY county_fips
),
pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
),
base AS (
  SELECT p.county_fips AS cfips, p.population AS population,
         COALESCE(d.major_disasters, 0) AS major_disasters,
         COALESCE(m.manuf_emp, 0) AS manuf_emp,
         COALESCE(e.ind_mwh, 0) AS ind_mwh
  FROM pop p
  LEFT JOIN disaster_counts d ON p.county_fips = d.county_fips
  LEFT JOIN manuf m ON p.county_fips = m.county_fips
  LEFT JOIN elec e ON p.county_fips = e.county_fips
  WHERE p.population >= 30000 AND COALESCE(m.manuf_emp,0) >= 5000
),
stats AS (
  SELECT median(major_disasters) AS med_disasters, count(*) AS n FROM base
)
SELECT * FROM stats

WITH disaster_counts AS (
  SELECT county_fips, COUNT(DISTINCT disaster_number) AS major_disasters
  FROM disasters.disaster_declarations
  WHERE declaration_type = 'DR' AND county_fips IS NOT NULL
  GROUP BY county_fips
),
manuf AS (
  SELECT area_fips AS county_fips, SUM(annual_avg_emplvl) AS manuf_emp
  FROM econ.county_qcew
  WHERE industry_code = '31-33' AND own_code = '5' AND "year" = '2024'
  GROUP BY area_fips
),
elec AS (
  SELECT county_fips, SUM(sales_industrial_mwh) AS ind_mwh
  FROM energy.eia_electricity_demand_by_county
  WHERE report_year = 2023
  GROUP BY county_fips
),
pop AS (
  SELECT county_fips, total_population AS population
  FROM census.acs_population
  WHERE "year" = '2023' AND geography = 'county'
),
base AS (
  SELECT p.county_fips AS cfips, p.population AS population,
         COALESCE(d.major_disasters, 0) AS major_disasters,
         COALESCE(m.manuf_emp, 0) AS manuf_emp,
         COALESCE(e.ind_mwh, 0) AS ind_mwh
  FROM pop p
  LEFT JOIN disaster_counts d ON p.county_fips = d.county_fips
  LEFT JOIN manuf m ON p.county_fips = m.county_fips
  LEFT JOIN elec e ON p.county_fips = e.county_fips
  WHERE p.population >= 30000 AND COALESCE(m.manuf_emp,0) >= 5000
),
qualifying AS (
  SELECT * FROM base WHERE major_disasters <= 2
),
scaled AS (
  SELECT cfips, population, major_disasters, manuf_emp, ind_mwh,
    (manuf_emp - MIN(manuf_emp) OVER()) * 1.0 / NULLIF(MAX(manuf_emp) OVER() - MIN(manuf_emp) OVER(),0) AS manuf_score,
    (ind_mwh - MIN(ind_mwh) OVER()) * 1.0 / NULLIF(MAX(ind_mwh) OVER() - MIN(ind_mwh) OVER(),0) AS elec_score
  FROM qualifying
)
SELECT s.cfips, c.county_name, c.state_fips,
       s.population, s.major_disasters, s.manuf_emp, ROUND(s.ind_mwh) AS ind_mwh,
       ROUND(manuf_score,3) AS manuf_score,
       ROUND(elec_score,3) AS elec_score,
       ROUND(0.6*manuf_score + 0.4*elec_score, 4) AS infra_score
FROM scaled s
JOIN geo.counties c ON s.cfips = c.county_fips
WHERE c."year" = '2023'
ORDER BY infra_score DESC
FETCH FIRST 20 ROWS ONLY

SELECT COUNT(*) AS null_fips_rows, COUNT(DISTINCT disaster_number) AS null_fips_disasters FROM disasters.disaster_declarations WHERE declaration_type = 'DR' AND county_fips IS NULL