130_nyt_kc_county_temp_1.sql

#standardSQL
-- Aggregate blockgroups to approximately capture the municipality of Kansas City, MO;
-- joins in blockgroup geometry and land/water area and ACS 2018 5-year data.
-- https://github.com/MEDSL/2018-elections-unoffical/blob/master/election-context-2018.csv
CREATE
OR REPLACE VIEW public.nyt_kc_county_temp_1 AS
SELECT
    ANY_VALUE(blockgroup.state_fips_code) as state_fips_code,
    "2000000" AS county_fips_code,
    "2000000" AS combined_fips_code,
    CAST(NULL AS STRING) AS county_gnis_code,
    CAST(NULL AS STRING) AS aff_geo_code,
    STRING_AGG(acs.geo_id) AS geo_id,
    'Kansas City' AS county_name,
    'Missouri' AS state_name,
    'MO' AS state_abbreviation,
    SUM(blockgroup.area_land_meters) AS area_land_meters,
    SUM(blockgroup.area_water_meters) AS area_water_meters,
    ST_X(ST_CENTROID_AGG(blockgroup.blockgroup_geom)) AS longitude,
    ST_Y(ST_CENTROID_AGG(blockgroup.blockgroup_geom)) AS latitude,
    ST_UNION_AGG(blockgroup.blockgroup_geom) AS county_geom,
    SUM(acs.nonfamily_households) AS nonfamily_households,
    SUM(acs.family_households) AS family_households,
    SUM(acs.median_year_structure_built * total_pop) / SUM(total_pop) AS median_year_structure_built,
    SUM(acs.rent_burden_not_computed) AS rent_burden_not_computed,
    SUM(acs.rent_over_50_percent) AS rent_over_50_percent,
    SUM(acs.rent_40_to_50_percent) AS rent_40_to_50_percent,
    SUM(acs.rent_35_to_40_percent) AS rent_35_to_40_percent,
    SUM(acs.rent_30_to_35_percent) AS rent_30_to_35_percent,
    SUM(acs.rent_25_to_30_percent) AS rent_25_to_30_percent,
    SUM(acs.rent_20_to_25_percent) AS rent_20_to_25_percent,
    SUM(acs.rent_15_to_20_percent) AS rent_15_to_20_percent,
    SUM(acs.rent_10_to_15_percent) AS rent_10_to_15_percent,
    SUM(acs.rent_under_10_percent) AS rent_under_10_percent,
    SUM(acs.total_pop) AS total_pop,
    SUM(acs.male_pop) AS male_pop,
    SUM(acs.female_pop) AS female_pop,
    SUM(acs.median_age) AS median_age,
    SUM(acs.white_pop) AS white_pop,
    SUM(acs.black_pop) AS black_pop,
    SUM(acs.asian_pop) AS asian_pop,
    SUM(acs.hispanic_pop) AS hispanic_pop,
    SUM(acs.amerindian_pop) AS amerindian_pop,
    SUM(acs.other_race_pop) AS other_race_pop,
    SUM(acs.two_or_more_races_pop) AS two_or_more_races_pop,
    SUM(acs.not_hispanic_pop) AS not_hispanic_pop,
    SUM(acs.commuters_by_public_transportation) AS commuters_by_public_transportation,
    SUM(acs.households) AS households,
    SUM(acs.median_income) AS median_income,
    SUM(acs.income_per_capita * total_pop) / SUM(total_pop) AS income_per_capita,
    SUM(acs.housing_units) AS housing_units,
    SUM(acs.vacant_housing_units) AS vacant_housing_units,
    SUM(acs.vacant_housing_units_for_rent) AS vacant_housing_units_for_rent,
    SUM(acs.vacant_housing_units_for_sale) AS vacant_housing_units_for_sale,
    SUM(acs.median_rent) AS median_rent,
    SUM(acs.percent_income_spent_on_rent * total_pop) / SUM(total_pop) AS percent_income_spent_on_rent,
    SUM(acs.owner_occupied_housing_units) AS owner_occupied_housing_units,
    SUM(acs.million_dollar_housing_units) AS million_dollar_housing_units,
    SUM(acs.mortgaged_housing_units) AS mortgaged_housing_units,
    SUM(acs.families_with_young_children) AS families_with_young_children,
    SUM(acs.two_parent_families_with_young_children) AS two_parent_families_with_young_children,
    SUM(
        acs.two_parents_in_labor_force_families_with_young_children
    ) AS two_parents_in_labor_force_families_with_young_children,
    SUM(
        acs.two_parents_father_in_labor_force_families_with_young_children
    ) AS two_parents_father_in_labor_force_families_with_young_children,
    SUM(
        acs.two_parents_mother_in_labor_force_families_with_young_children
    ) AS two_parents_mother_in_labor_force_families_with_young_children,
    SUM(
        acs.two_parents_not_in_labor_force_families_with_young_children
    ) AS two_parents_not_in_labor_force_families_with_young_children,
    SUM(acs.one_parent_families_with_young_children) AS one_parent_families_with_young_children,
    SUM(
        acs.father_one_parent_families_with_young_children
    ) AS father_one_parent_families_with_young_children,
    SUM(
        acs.father_in_labor_force_one_parent_families_with_young_children
    ) AS father_in_labor_force_one_parent_families_with_young_children,
    SUM(acs.commute_10_14_mins) AS commute_10_14_mins,
    SUM(acs.commute_15_19_mins) AS commute_15_19_mins,
    SUM(acs.commute_20_24_mins) AS commute_20_24_mins,
    SUM(acs.commute_25_29_mins) AS commute_25_29_mins,
    SUM(acs.commute_30_34_mins) AS commute_30_34_mins,
    SUM(acs.commute_45_59_mins) AS commute_45_59_mins,
    SUM(acs.aggregate_travel_time_to_work) AS aggregate_travel_time_to_work,
    SUM(acs.income_less_10000) AS income_less_10000,
    SUM(acs.income_10000_14999) AS income_10000_14999,
    SUM(acs.income_15000_19999) AS income_15000_19999,
    SUM(acs.income_20000_24999) AS income_20000_24999,
    SUM(acs.income_25000_29999) AS income_25000_29999,
    SUM(acs.income_30000_34999) AS income_30000_34999,
    SUM(acs.income_35000_39999) AS income_35000_39999,
    SUM(acs.income_40000_44999) AS income_40000_44999,
    SUM(acs.income_45000_49999) AS income_45000_49999,
    SUM(acs.income_50000_59999) AS income_50000_59999,
    SUM(acs.income_60000_74999) AS income_60000_74999,
    SUM(acs.income_75000_99999) AS income_75000_99999,
    SUM(acs.income_100000_124999) AS income_100000_124999,
    SUM(acs.income_125000_149999) AS income_125000_149999,
    SUM(acs.income_150000_199999) AS income_150000_199999,
    SUM(acs.income_200000_or_more) AS income_200000_or_more,
    SUM(
        acs.renter_occupied_housing_units_paying_cash_median_gross_rent * total_pop
    ) / SUM(total_pop) AS renter_occupied_housing_units_paying_cash_median_gross_rent,
    SUM(
        acs.owner_occupied_housing_units_lower_value_quartile
     * total_pop) / SUM(total_pop) AS owner_occupied_housing_units_lower_value_quartile,
    SUM(acs.owner_occupied_housing_units_median_value * total_pop) / SUM(total_pop) AS owner_occupied_housing_units_median_value,
    SUM(
        acs.owner_occupied_housing_units_upper_value_quartile
     * total_pop) / SUM(total_pop) AS owner_occupied_housing_units_upper_value_quartile,
    SUM(acs.married_households) AS married_households,
    SUM(acs.occupied_housing_units) AS occupied_housing_units,
    SUM(acs.housing_units_renter_occupied) AS housing_units_renter_occupied,
    SUM(acs.dwellings_1_units_detached) AS dwellings_1_units_detached,
    SUM(acs.dwellings_1_units_attached) AS dwellings_1_units_attached,
    SUM(acs.dwellings_2_units) AS dwellings_2_units,
    SUM(acs.dwellings_3_to_4_units) AS dwellings_3_to_4_units,
    SUM(acs.dwellings_5_to_9_units) AS dwellings_5_to_9_units,
    SUM(acs.dwellings_10_to_19_units) AS dwellings_10_to_19_units,
    SUM(acs.dwellings_20_to_49_units) AS dwellings_20_to_49_units,
    SUM(acs.dwellings_50_or_more_units) AS dwellings_50_or_more_units,
    SUM(acs.mobile_homes) AS mobile_homes,
    SUM(acs.housing_built_2005_or_later) AS housing_built_2005_or_later,
    SUM(acs.housing_built_2000_to_2004) AS housing_built_2000_to_2004,
    SUM(acs.housing_built_1939_or_earlier) AS housing_built_1939_or_earlier,
    SUM(acs.male_under_5) AS male_under_5,
    SUM(acs.male_5_to_9) AS male_5_to_9,
    SUM(acs.male_10_to_14) AS male_10_to_14,
    SUM(acs.male_15_to_17) AS male_15_to_17,
    SUM(acs.male_18_to_19) AS male_18_to_19,
    SUM(acs.male_20) AS male_20,
    SUM(acs.male_21) AS male_21,
    SUM(acs.male_22_to_24) AS male_22_to_24,
    SUM(acs.male_25_to_29) AS male_25_to_29,
    SUM(acs.male_30_to_34) AS male_30_to_34,
    SUM(acs.male_35_to_39) AS male_35_to_39,
    SUM(acs.male_40_to_44) AS male_40_to_44,
    SUM(acs.male_45_to_49) AS male_45_to_49,
    SUM(acs.male_50_to_54) AS male_50_to_54,
    SUM(acs.male_55_to_59) AS male_55_to_59,
    SUM(acs.male_60_61) AS male_60_61,
    SUM(acs.male_62_64) AS male_62_64,
    SUM(acs.male_65_to_66) AS male_65_to_66,
    SUM(acs.male_67_to_69) AS male_67_to_69,
    SUM(acs.male_70_to_74) AS male_70_to_74,
    SUM(acs.male_75_to_79) AS male_75_to_79,
    SUM(acs.male_80_to_84) AS male_80_to_84,
    SUM(acs.male_85_and_over) AS male_85_and_over,
    SUM(acs.female_under_5) AS female_under_5,
    SUM(acs.female_5_to_9) AS female_5_to_9,
    SUM(acs.female_10_to_14) AS female_10_to_14,
    SUM(acs.female_15_to_17) AS female_15_to_17,
    SUM(acs.female_18_to_19) AS female_18_to_19,
    SUM(acs.female_20) AS female_20,
    SUM(acs.female_21) AS female_21,
    SUM(acs.female_22_to_24) AS female_22_to_24,
    SUM(acs.female_25_to_29) AS female_25_to_29,
    SUM(acs.female_30_to_34) AS female_30_to_34,
    SUM(acs.female_35_to_39) AS female_35_to_39,
    SUM(acs.female_40_to_44) AS female_40_to_44,
    SUM(acs.female_45_to_49) AS female_45_to_49,
    SUM(acs.female_50_to_54) AS female_50_to_54,
    SUM(acs.female_55_to_59) AS female_55_to_59,
    SUM(acs.female_60_to_61) AS female_60_to_61,
    SUM(acs.female_62_to_64) AS female_62_to_64,
    SUM(acs.female_65_to_66) AS female_65_to_66,
    SUM(acs.female_67_to_69) AS female_67_to_69,
    SUM(acs.female_70_to_74) AS female_70_to_74,
    SUM(acs.female_75_to_79) AS female_75_to_79,
    SUM(acs.female_80_to_84) AS female_80_to_84,
    SUM(acs.female_85_and_over) AS female_85_and_over,
    SUM(acs.white_including_hispanic) AS white_including_hispanic,
    SUM(acs.black_including_hispanic) AS black_including_hispanic,
    SUM(acs.amerindian_including_hispanic) AS amerindian_including_hispanic,
    SUM(acs.asian_including_hispanic) AS asian_including_hispanic,
    SUM(acs.commute_5_9_mins) AS commute_5_9_mins,
    SUM(acs.commute_35_39_mins) AS commute_35_39_mins,
    SUM(acs.commute_40_44_mins) AS commute_40_44_mins,
    SUM(acs.commute_60_89_mins) AS commute_60_89_mins,
    SUM(acs.commute_90_more_mins) AS commute_90_more_mins,
    SUM(acs.households_retirement_income) AS households_retirement_income,
    SUM(acs.armed_forces) AS armed_forces,
    SUM(acs.civilian_labor_force) AS civilian_labor_force,
    SUM(acs.employed_pop) AS employed_pop,
    SUM(acs.unemployed_pop) AS unemployed_pop,
    SUM(acs.not_in_labor_force) AS not_in_labor_force,
    SUM(acs.pop_16_over) AS pop_16_over,
    SUM(acs.pop_in_labor_force) AS pop_in_labor_force,
    NULL AS asian_male_45_54,
    NULL AS asian_male_55_64,
    NULL AS black_male_45_54,
    NULL AS black_male_55_64,
    NULL AS hispanic_male_45_54,
    NULL AS hispanic_male_55_64,
    NULL AS white_male_45_54,
    NULL AS white_male_55_64,
    NULL AS bachelors_degree_2,
    NULL AS bachelors_degree_or_higher_25_64,
    NULL AS children,
    NULL AS children_in_single_female_hh,
    NULL AS commuters_by_bus,
    NULL AS commuters_by_car_truck_van,
    NULL AS commuters_by_carpool,
    NULL AS commuters_by_subway_or_elevated,
    NULL AS commuters_drove_alone,
    NULL AS different_house_year_ago_different_city,
    NULL AS different_house_year_ago_same_city,
    NULL AS employed_agriculture_forestry_fishing_hunting_mining,
    NULL AS employed_arts_entertainment_recreation_accommodation_food,
    NULL AS employed_construction,
    NULL AS employed_education_health_social,
    NULL AS employed_finance_insurance_real_estate,
    NULL AS employed_information,
    NULL AS employed_manufacturing,
    NULL AS employed_other_services_not_public_admin,
    NULL AS employed_public_administration,
    NULL AS employed_retail_trade,
    NULL AS employed_science_management_admin_waste,
    NULL AS employed_transportation_warehousing_utilities,
    NULL AS employed_wholesale_trade,
    NULL AS female_female_households,
    NULL AS four_more_cars,
    NULL AS gini_index,
    NULL AS graduate_professional_degree,
    NULL AS group_quarters,
    NULL AS high_school_including_ged,
    NULL AS households_public_asst_or_food_stamps,
    NULL AS in_grades_1_to_4,
    NULL AS in_grades_5_to_8,
    NULL AS in_grades_9_to_12,
    NULL AS in_school,
    NULL AS in_undergrad_college,
    NULL AS less_than_high_school_graduate,
    NULL AS male_45_64_associates_degree,
    NULL AS male_45_64_bachelors_degree,
    NULL AS male_45_64_graduate_degree,
    NULL AS male_45_64_less_than_9_grade,
    NULL AS male_45_64_grade_9_12,
    NULL AS male_45_64_high_school,
    NULL AS male_45_64_some_college,
    NULL AS male_45_to_64,
    NULL AS male_male_households,
    NULL AS management_business_sci_arts_employed,
    NULL AS no_car,
    NULL AS no_cars,
    NULL AS not_us_citizen_pop,
    NULL AS occupation_management_arts,
    NULL AS occupation_natural_resources_construction_maintenance,
    NULL AS occupation_production_transportation_material,
    NULL AS occupation_sales_office,
    NULL AS occupation_services,
    NULL AS one_car,
    NULL AS two_cars,
    NULL AS three_cars,
    NULL AS pop_25_64,
    NULL AS pop_determined_poverty_status,
    NULL AS population_1_year_and_over,
    NULL AS population_3_years_over,
    NULL AS poverty,
    NULL AS sales_office_employed,
    NULL AS some_college_and_associates_degree,
    NULL AS walked_to_work,
    NULL AS worked_at_home,
    NULL AS workers_16_and_over,
    SUM(acs.associates_degree) AS associates_degree,
    SUM(acs.bachelors_degree) AS bachelors_degree,
    SUM(acs.high_school_diploma) AS high_school_diploma,
    SUM(acs.less_one_year_college) AS less_one_year_college,
    SUM(acs.masters_degree) AS masters_degree,
    SUM(acs.one_year_more_college) AS one_year_more_college,
    SUM(acs.pop_25_years_over) AS pop_25_years_over,
    SUM(acs.commute_35_44_mins) AS commute_35_44_mins,
    SUM(acs.commute_60_more_mins) AS commute_60_more_mins,
    SUM(acs.commute_less_10_mins) AS commute_less_10_mins,
    SUM(acs.commuters_16_over) AS commuters_16_over,
    NULL AS hispanic_any_race,
    NULL AS pop_5_years_over,
    NULL AS speak_only_english_at_home,
    NULL AS speak_spanish_at_home,
    NULL AS speak_spanish_at_home_low_english,
    NULL AS pop_15_and_over,
    NULL AS pop_never_married,
    NULL AS pop_now_married,
    NULL AS pop_separated,
    NULL AS pop_widowed,
    NULL AS pop_divorced,
    MIN(acs.do_date) AS do_date
FROM
    `bigquery-public-data.census_bureau_acs.blockgroup_2017_5yr` acs
LEFT JOIN `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` blockgroup on acs.geo_id=blockgroup.geo_id
WHERE
    acs.geo_id IN (
        SELECT
            geo_id
        FROM
            `covid-project-275201`.public.kansas_city_blockgroups
    );