Database Reference
In-Depth Information
You can modify the query to generate results for multiple cities of interest
by adjusting the filter condition.
SELECT * FROM (
SELECT primary_city, zip,
RATIO_TO_REPORT(population) OVER() AS fraction
FROM [bigquery-e2e:reference.zip_codes]
WHERE primary_city IN ('Bellevue', 'Kirkland',
'Seattle'))
ORDER BY primary_city, fraction DESC
Currently, the use of window functions is not compatible with a top-level
ORDER BY
clause, so you need to use a subquery to order the final result.
However, this gives different results from the previous query for Seattle
alone. This is because now the population is being normalized with respect
to the total population of Bellevue, Kirkland, and Seattle. To instead
normalize the population with respect to the population of the city
corresponding to each row, you need the
PARTITION BY
clause.
SELECT * FROM (
SELECT primary_city, zip,
RATIO_TO_REPORT(population)
OVER(PARTITION BY primary_city) AS
city_fraction
FROM [bigquery-e2e:reference.zip_codes]
WHERE primary_city IN ('Bellevue', 'Kirkland',
'Seattle'))
ORDER BY primary_city, city_fraction DESC
This will treat rows with the same primary city as a window and use the total
population of each window to normalize rows in the window.
Next, turn your attention to finding the three most populated ZIP codes in
each state. Because you are ranking ZIP codes by population, you need the
ORDER BY
clause.
SELECT state, zip, population FROM (
SELECT
state, zip, population,
RANK() OVER (PARTITION BY state
