CensusGPT

WITH total_population AS (

  SELECT geo_id, SUM(white_population + black_population + native_american_population + asian_population + hispanic_population + two_or_more_population) AS total_population

  FROM population_by_race

  GROUP BY geo_id

),

racial_diversity AS (

  SELECT location_data.zip_code, 

      (population_by_race.white_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_white,

      (population_by_race.black_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_black,

      (population_by_race.native_american_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_native_american,

      (population_by_race.asian_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_asian,

      (population_by_race.hispanic_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_hispanic,

      (population_by_race.two_or_more_population / NULLIF(total_population.total_population, 0)) * 100 AS percentage_two_or_more,

      (population_by_race.white_population + population_by_race.black_population + population_by_race.native_american_population + population_by_race.asian_population + population_by_race.hispanic_population + population_by_race.two_or_more_population) AS total_racial_population

  FROM population_by_race

  JOIN location_data ON population_by_race.geo_id = location_data.geo_id

  JOIN total_population ON population_by_race.geo_id = total_population.geo_id

  WHERE location_data.city = 'San Francisco' AND location_data.state = 'CA'

)

SELECT zip_code, percentage_white, percentage_black, percentage_native_american, percentage_asian, percentage_hispanic, percentage_two_or_more

FROM racial_diversity

WHERE total_racial_population = (SELECT MAX(total_racial_population) FROM racial_diversity);