Approach for pulling ACS data from CensusReporter database into user-friendly tables in our own schema. By using the ACS documentation, this approach can be used to pull a lot of other sorts of data in useful ways.

censusreporter_acs_tables_to_user_friendly_tables.sql

--DROP SCHEMA IF EXISTS acs2018_5yr_extraction CASCADE;

CREATE SCHEMA acs2018_5yr_extraction;

CREATE TABLE acs2018_5yr_extraction.population__bg_tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract, blkgrp) AS geoid, b01003001 AS population, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01003 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 150
)
;
INSERT INTO acs2018_5yr_extraction.population__bg_tract_county 
   (sumlevel, geoid, population, stusab)
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid, b01003001 AS population, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01003 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
;
INSERT INTO acs2018_5yr_extraction.population__bg_tract_county 
   (sumlevel, geoid, population, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid, b01003001 AS population, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01003 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.median_income__bg_tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract, blkgrp) AS geoid, b19013001 AS median_income, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b19013 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 150
)
;
INSERT INTO acs2018_5yr_extraction.median_income__bg_tract_county
   (sumlevel, geoid, median_income, stusab)
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid, b19013001 AS median_income, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b19013 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
;
INSERT INTO acs2018_5yr_extraction.median_income__bg_tract_county
   (sumlevel, geoid, median_income, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid, b19013001 AS median_income, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b19013 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.median_age__bg_tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract, blkgrp) AS geoid, b01002001 AS median_age, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01002 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 150
)
;
INSERT INTO acs2018_5yr_extraction.median_age__bg_tract_county
   (sumlevel, geoid, median_age, stusab)
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid, b01002001 AS median_age, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01002 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
;
INSERT INTO acs2018_5yr_extraction.median_age__bg_tract_county
   (sumlevel, geoid, median_age, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid, b01002001 AS median_age, stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.b01002 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.spanish_speakers__bg_tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract, blkgrp) AS geoid
   , B16004004+B16004026+B16004048 AS total_spanish_speakers
   , B16004005+B16004027+B16004049+B16004006+B16004028+B16004050 AS speak_english_well
   , B16004007+B16004029+B16004051+B16004008+B16004030+B16004052 AS little_or_no_english
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B16004 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 150
)
;
INSERT INTO acs2018_5yr_extraction.spanish_speakers__bg_tract_county
  (sumlevel, geoid, total_spanish_speakers, speak_english_well, little_or_no_english, stusab)
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , B16004004+B16004026+B16004048 AS total_spanish_speakers
   , B16004005+B16004027+B16004049+B16004006+B16004028+B16004050 AS speak_english_well
   , B16004007+B16004029+B16004051+B16004008+B16004030+B16004052 AS little_or_no_english
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B16004 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
;
INSERT INTO acs2018_5yr_extraction.spanish_speakers__bg_tract_county
  (sumlevel, geoid, total_spanish_speakers, speak_english_well, little_or_no_english, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , B16004004+B16004026+B16004048 AS total_spanish_speakers
   , B16004005+B16004027+B16004049+B16004006+B16004028+B16004050 AS speak_english_well
   , B16004007+B16004029+B16004051+B16004008+B16004030+B16004052 AS little_or_no_english
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B16004 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.households_recieving_food_stamps_snap__bg_tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract, blkgrp) AS geoid
   , B22010001 AS total_households
   , B22010002 AS received_food_stamps
   , B22010005 AS did_not_receive_food_stamps
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B22010 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 150
)
;
INSERT INTO acs2018_5yr_extraction.households_recieving_food_stamps_snap__bg_tract_county
  (sumlevel, geoid, total_households, received_food_stamps, did_not_receive_food_stamps)
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , B22010001 AS total_households
   , B22010002 AS received_food_stamps
   , B22010005 AS did_not_receive_food_stamps
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B22010 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
;
INSERT INTO acs2018_5yr_extraction.households_recieving_food_stamps_snap__bg_tract_county
  (sumlevel, geoid, total_households, received_food_stamps, did_not_receive_food_stamps)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , B22010001 AS total_households
   , B22010002 AS received_food_stamps
   , B22010005 AS did_not_receive_food_stamps
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B22010 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;


CREATE TABLE acs2018_5yr_extraction.latino_origin__tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , B03001003 AS total_latino_population
   , B03001004 AS mexican
   , B03001005 AS puerto_rican
   , B03001006 AS cuban
   , B03001007 AS dominican
   , B03001009 AS costa_rican
   , B03001010 AS guatemalan
   , B03001011 AS honduran
   , B03001012 AS nicaraguan
   , B03001013 AS panamanian
   , B03001014 AS salvadoran
   , B03001017 AS argentinean
   , B03001018 AS bolivian
   , B03001019 AS chilean
   , B03001020 AS colombian
   , B03001021 AS ecuadorian
   , B03001023 AS peruvian
   , B03001025 AS venezuelan
   , B03001022 + B03001024 + B03001015 + B03001026 + B03001027 AS other_latino
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B03001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
)
;
INSERT INTO acs2018_5yr_extraction.latino_origin__tract_county
  (sumlevel, geoid, total_latino_population, mexican, puerto_rican, cuban, dominican, costa_rican, guatemalan, honduran, nicaraguan, panamanian, salvadoran, argentinean, bolivian, chilean, colombian, ecuadorian, peruvian, venezuelan, other_latino, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , B03001003 AS total_latino_population
   , B03001004 AS mexican
   , B03001005 AS puerto_rican
   , B03001006 AS cuban
   , B03001007 AS dominican
   , B03001009 AS costa_rican
   , B03001010 AS guatemalan
   , B03001011 AS honduran
   , B03001012 AS nicaraguan
   , B03001013 AS panamanian
   , B03001014 AS salvadoran
   , B03001017 AS argentinean
   , B03001018 AS bolivian
   , B03001019 AS chilean
   , B03001020 AS colombian
   , B03001021 AS ecuadorian
   , B03001023 AS peruvian
   , B03001025 AS venezuelan
   , B03001022 + B03001024 + B03001015 + B03001026 + B03001027 AS other_latino
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B03001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.moved_within_past_year__tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , B07001001 AS total_population
   , B07001017 AS did_not_move
   , B07001033 AS moved_within_county
   , B07001049 AS moved_within_state
   , B07001065 AS moved_from_different_state
   , B07001081 AS moved_from_abroad
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B07001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
)
;
INSERT INTO acs2018_5yr_extraction.moved_within_past_year__tract_county
  (sumlevel, geoid, total_population, did_not_move, moved_within_county, moved_within_state, moved_from_different_state, moved_from_abroad, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , B07001001 AS total_population
   , B07001017 AS did_not_move
   , B07001033 AS moved_within_county
   , B07001049 AS moved_within_state
   , B07001065 AS moved_from_different_state
   , B07001081 AS moved_from_abroad
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B07001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.language_spoken_at_home__tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , C16001001 AS total_population
   , C16001002 AS english_only
   , C16001003 AS spanish
   , C16001006 AS french
   , C16001009 AS german
   , C16001012 AS russian_or_other_slavic
   , C16001015 AS other_indo_european
   , C16001018 AS korean
   , C16001021 AS chinese
   , C16001024 AS vietnamese
   , C16001027 AS tagalog
   , C16001030 AS other_aapi
   , C16001033 AS arabic
   , C16001036 AS other_and_unspecified
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.C16001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
)
;
INSERT INTO acs2018_5yr_extraction.language_spoken_at_home__tract_county
  (sumlevel, geoid, total_population, english_only, spanish, french, german, russian_or_other_slavic, other_indo_european, korean, chinese, vietnamese, tagalog, other_aapi, arabic, other_and_unspecified, stusab)
   SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , C16001001 AS total_population
   , C16001002 AS english_only
   , C16001003 AS spanish
   , C16001006 AS french
   , C16001009 AS german
   , C16001012 AS russian_or_other_slavic
   , C16001015 AS other_indo_european
   , C16001018 AS korean
   , C16001021 AS chinese
   , C16001024 AS vietnamese
   , C16001027 AS tagalog
   , C16001030 AS other_aapi
   , C16001033 AS arabic
   , C16001036 AS other_and_unspecified
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.C16001 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.ancestry_first_reported__tract_county AS (
   SELECT gh.sumlevel, CONCAT(state, county, tract) AS geoid
   , B04006001 AS total
   , B04006002 AS afghan
   , B04006003 AS albanian
   , B04006004 AS alsatian
   , B04006005 AS american
   , B04006007 AS egyptian
   , B04006008 AS iraqi
   , B04006009 AS jordanian
   , B04006010 AS lebanese
   , B04006011 AS moroccan
   , B04006012 AS palestinian
   , B04006013 AS syrian
   , B04006014 AS arab
   , B04006015 AS other_arab
   , B04006016 AS armenian
   , B04006017 AS assyrian_or_chaldean_or_syriac
   , B04006018 AS australian
   , B04006019 AS austrian
   , B04006020 AS basque
   , B04006021 AS belgian
   , B04006022 AS brazilian
   , B04006023 AS british
   , B04006024 AS bulgarian
   , B04006025 AS cajun
   , B04006026 AS canadian
   , B04006027 AS carpatho_rusyn
   , B04006028 AS celtic
   , B04006029 AS croatian
   , B04006030 AS cypriot
   , B04006031 AS czech
   , B04006032 AS czechoslovakian
   , B04006033 AS danish
   , B04006034 AS dutch
   , B04006035 AS eastern_european
   , B04006036 AS english
   , B04006037 AS estonian
   , B04006038 AS european
   , B04006039 AS finnish
   , B04006040 AS french_except_basque
   , B04006041 AS french_canadian
   , B04006042 AS german
   , B04006043 AS german_russian
   , B04006044 AS greek
   , B04006045 AS guyanese
   , B04006046 AS hungarian
   , B04006047 AS icelander
   , B04006048 AS iranian
   , B04006049 AS irish
   , B04006050 AS israeli
   , B04006051 AS italian
   , B04006052 AS latvian
   , B04006053 AS lithuanian
   , B04006054 AS luxemburger
   , B04006055 AS macedonian
   , B04006056 AS maltese
   , B04006057 AS new_zealander
   , B04006058 AS northern_european
   , B04006059 AS norwegian
   , B04006060 AS pennsylvania_german
   , B04006061 AS polish
   , B04006062 AS portuguese
   , B04006063 AS romanian
   , B04006064 AS russian
   , B04006065 AS scandinavian
   , B04006066 AS scotch_irish
   , B04006067 AS scottish
   , B04006068 AS serbian
   , B04006069 AS slavic
   , B04006070 AS slovak
   , B04006071 AS slovene
   , B04006072 AS soviet_union
   , B04006074 AS cape_verdean
   , B04006075 AS ethiopian
   , B04006076 AS ghanaian
   , B04006077 AS kenyan
   , B04006078 AS liberian
   , B04006079 AS nigerian
   , B04006080 AS senegalese
   , B04006081 AS sierra_leonean
   , B04006082 AS somalian
   , B04006083 AS south_african
   , B04006084 AS sudanese
   , B04006085 AS ugandan
   , B04006086 AS zimbabwean
   , B04006087 AS african
   , B04006088 AS other_subsaharan_african
   , B04006089 AS swedish
   , B04006090 AS swiss
   , B04006091 AS turkish
   , B04006092 AS ukrainian
   , B04006093 AS welsh
   , B04006095 AS bahamian
   , B04006096 AS barbadian
   , B04006097 AS belizean
   , B04006098 AS bermudan
   , B04006099 AS british_west_indian
   , B04006100 AS dutch_west_indian
   , B04006101 AS haitian
   , B04006102 AS jamaican
   , B04006103 AS trinidadian_and_tobagonian
   , B04006104 AS us_virgin_islander
   , B04006105 AS west_indian
   , B04006106 AS other_west_indian
   , B04006107 AS yugoslavian
   , B04006108 AS other_groups
   , B04006109 AS unclassified_or_not_reported
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B04006 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 140
)
;
INSERT INTO acs2018_5yr_extraction.ancestry_first_reported__tract_county
  (sumlevel, geoid, total, afghan, albanian, alsatian, american, egyptian, iraqi, jordanian, lebanese, moroccan, palestinian, syrian, arab,other_arab, armenian, assyrian_or_chaldean_or_syriac, australian, austrian, basque, belgian, brazilian, british, bulgarian, cajun, canadian, carpatho_rusyn, celtic, croatian, cypriot, czech, czechoslovakian, danish, dutch, eastern_european, english, estonian, european, finnish, french_except_basque, french_canadian, german, german_russian, greek, guyanese, hungarian, icelander, iranian, irish, israeli, italian, latvian, lithuanian, luxemburger, macedonian, maltese, new_zealander, northern_european, norwegian, pennsylvania_german, polish, portuguese, romanian, russian, scandinavian, scotch_irish, scottish, serbian, slavic, slovak, slovene, soviet_union, cape_verdean, ethiopian, ghanaian, kenyan, liberian, nigerian, senegalese, sierra_leonean, somalian, south_african, sudanese, ugandan, zimbabwean, african, other_subsaharan_african, swedish, swiss, turkish, ukrainian, welsh, bahamian, barbadian, belizean, bermudan, british_west_indian, dutch_west_indian, haitian, jamaican, trinidadian_and_tobagonian, us_virgin_islander, west_indian, other_west_indian, yugoslavian, other_groups, unclassified_or_not_reported, stusab)
SELECT gh.sumlevel, CONCAT(state, county) AS geoid
   , B04006001 AS total
   , B04006002 AS afghan
   , B04006003 AS albanian
   , B04006004 AS alsatian
   , B04006005 AS american
   , B04006007 AS egyptian
   , B04006008 AS iraqi
   , B04006009 AS jordanian
   , B04006010 AS lebanese
   , B04006011 AS moroccan
   , B04006012 AS palestinian
   , B04006013 AS syrian
   , B04006014 AS arab
   , B04006015 AS other_arab
   , B04006016 AS armenian
   , B04006017 AS assyrian_or_chaldean_or_syriac
   , B04006018 AS australian
   , B04006019 AS austrian
   , B04006020 AS basque
   , B04006021 AS belgian
   , B04006022 AS brazilian
   , B04006023 AS british
   , B04006024 AS bulgarian
   , B04006025 AS cajun
   , B04006026 AS canadian
   , B04006027 AS carpatho_rusyn
   , B04006028 AS celtic
   , B04006029 AS croatian
   , B04006030 AS cypriot
   , B04006031 AS czech
   , B04006032 AS czechoslovakian
   , B04006033 AS danish
   , B04006034 AS dutch
   , B04006035 AS eastern_european
   , B04006036 AS english
   , B04006037 AS estonian
   , B04006038 AS european
   , B04006039 AS finnish
   , B04006040 AS french_except_basque
   , B04006041 AS french_canadian
   , B04006042 AS german
   , B04006043 AS german_russian
   , B04006044 AS greek
   , B04006045 AS guyanese
   , B04006046 AS hungarian
   , B04006047 AS icelander
   , B04006048 AS iranian
   , B04006049 AS irish
   , B04006050 AS israeli
   , B04006051 AS italian
   , B04006052 AS latvian
   , B04006053 AS lithuanian
   , B04006054 AS luxemburger
   , B04006055 AS macedonian
   , B04006056 AS maltese
   , B04006057 AS new_zealander
   , B04006058 AS northern_european
   , B04006059 AS norwegian
   , B04006060 AS pennsylvania_german
   , B04006061 AS polish
   , B04006062 AS portuguese
   , B04006063 AS romanian
   , B04006064 AS russian
   , B04006065 AS scandinavian
   , B04006066 AS scotch_irish
   , B04006067 AS scottish
   , B04006068 AS serbian
   , B04006069 AS slavic
   , B04006070 AS slovak
   , B04006071 AS slovene
   , B04006072 AS soviet_union
   , B04006074 AS cape_verdean
   , B04006075 AS ethiopian
   , B04006076 AS ghanaian
   , B04006077 AS kenyan
   , B04006078 AS liberian
   , B04006079 AS nigerian
   , B04006080 AS senegalese
   , B04006081 AS sierra_leonean
   , B04006082 AS somalian
   , B04006083 AS south_african
   , B04006084 AS sudanese
   , B04006085 AS ugandan
   , B04006086 AS zimbabwean
   , B04006087 AS african
   , B04006088 AS other_subsaharan_african
   , B04006089 AS swedish
   , B04006090 AS swiss
   , B04006091 AS turkish
   , B04006092 AS ukrainian
   , B04006093 AS welsh
   , B04006095 AS bahamian
   , B04006096 AS barbadian
   , B04006097 AS belizean
   , B04006098 AS bermudan
   , B04006099 AS british_west_indian
   , B04006100 AS dutch_west_indian
   , B04006101 AS haitian
   , B04006102 AS jamaican
   , B04006103 AS trinidadian_and_tobagonian
   , B04006104 AS us_virgin_islander
   , B04006105 AS west_indian
   , B04006106 AS other_west_indian
   , B04006107 AS yugoslavian
   , B04006108 AS other_groups
   , B04006109 AS unclassified_or_not_reported
   , stusab
   FROM acs2018_5yr.geoheader gh
   JOIN acs2018_5yr.B04006 b
   ON gh.geoid = b.geoid
   WHERE gh.sumlevel = 50
;

CREATE TABLE acs2018_5yr_extraction.african_groups__tract_county AS (
   SELECT sumlevel, geoid
   , somalian
   , kenyan
   , ethiopian
   , somalian + kenyan + ethiopian AS east_african
   , nigerian
   , sierra_leonean
   , ghanaian
   , liberian
   , nigerian + sierra_leonean + ghanaian + liberian AS west_african
   , cape_verdean
   , senegalese
   , south_african
   , sudanese
   , ugandan
   , zimbabwean
   , other_subsaharan_african
   , somalian + kenyan + ethiopian 
   + nigerian + sierra_leonean + ghanaian + liberian 
   + cape_verdean + senegalese + south_african
   + sudanese + ugandan + zimbabwean 
   + other_subsaharan_african AS sum_of_african_nationalities
   , total
   , stusab
   FROM acs2018_5yr_extraction.ancestry_first_reported__tract_county
)
;