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);

WITH gender_ratio AS (

  SELECT location_data.zip_code, (population_by_gender.female_population / NULLIF(population_by_gender.male_population, 0)) AS female_to_male_ratio

  FROM location_data

  JOIN population_by_gender ON location_data.geo_id = population_by_gender.geo_id

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

)

SELECT zip_code, female_to_male_ratio

FROM gender_ratio

ORDER BY female_to_male_ratio DESC NULLS LAST

LIMIT 3;

WITH florida_cities AS (

  SELECT city, state, 

      (violent_crime + murder_and_nonnegligent_manslaughter + rape + robbery + aggravated_assault + property_crime + burglary + larceny_theft + motor_vehicle_theft + arson) AS total_crime

  FROM crime_by_city

  WHERE state = 'FL'

)

SELECT city, state, total_crime

FROM florida_cities

ORDER BY total_crime DESC NULLS LAST

LIMIT 5;