Ranking cities by percent of their state population in SQL
Question
You're given the following table showing the population of various cities in the US, along with their associated states:
Table: population_table
| state_name | city_name | total_population |
|---|---|---|
| California | Los Angeles | 4000000 |
| California | San Francisco | 1000000 |
| California | Sacramento | 500000 |
| New York | New York City | 8300000 |
| New York | Buffalo | 250000 |
| New York | Rochester | 200000 |
| Texas | Austin | 1000000 |
| Texas | Dallas | 1300000 |
| Texas | Houston | 2300000 |
Using this table, write a SQL query to return the following:
| state_name | city_name | total_population | percent_of_state_population |
|---|---|---|---|
| ... | ... | the total population of the given city... | the percent of the total state population the given city represents... |
You can view/query the table in an interactive SQL fiddle here.