Tips for Writing SQL Queries

population_by_zip_2010

giving population information by zip code, and

zipcode_area

giving geo-info by zip code

• What type of join is appropriate
• Which key(s) to join on

To do that, we need to join the two tables (population by zip) and (state/geo-info by zip) together, with zip code being the obvious key to use to join them. In the population by zip table, the zip code field is called “zipcode”, and in the state/geo-info by zip table, the zip code field is also called “zipcode”. For now, we have just a query fragment:

...
FROM
  [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
JOIN
  [bigquery-public-data:utility_us.zipcode_area] GEO
ON
  POP.zipcode = GEO.zipcode
...

• Any initial filtering of tables through WHERE
• If aggregation required thereafter, which key(s) to use in forming groups/buckets in GROUP BY

...
FROM
  [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
JOIN
  [bigquery-public-data:utility_us.zipcode_area] GEO
ON
  POP.zipcode = GEO.zipcode
WHERE
  GEO.state_code = 'NC'
...

Let’s simultaneously consider another question — how many people are there in each state? There are multiple zip codes that belong to the same state. We want to gather all of the rows (zip codes) belonging to the same state and look at them together, and do this for each unique state. That’s what GROUP BY is for. The arguments to GROUP BY are columns where each unique combination of values of those columns constitutes a new grouping. In our case, we will only provide state_code as an argument, so we look at groups for each unique value of state_code:

...
FROM
  [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
JOIN
  [bigquery-public-data:utility_us.zipcode_area] GEO
ON
  POP.zipcode = GEO.zipcode
GROUP BY
  GEO.state_code
...

• To order/sort the results, use ORDER BY
• To filter based on values after aggregation, use HAVING

...
FROM
  [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
JOIN
  [bigquery-public-data:utility_us.zipcode_area] GEO
ON
  POP.zipcode = GEO.zipcode
WHERE
  GEO.state_code = 'NC'
ORDER BY
  GEO.zipcode, POP.gender
...

In our other example, let’s imagine that we will eventually sum up the population within each state for both genders, but we only want to keep those states & their total populations if that total population exceeds 10 million:

...
FROM
  [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
JOIN
  [bigquery-public-data:utility_us.zipcode_area] GEO
ON
  POP.zipcode = GEO.zipcode
GROUP BY
  GEO.state_code
HAVING
  total_pop > 10000000
...

What fields do we want from North Carolina zip codes? Let’s say, the zip code, gender, population for that gender,
the state code (just to reassure ourselves we only see ‘NC’). And maybe, because we can, let’s include city.

SELECT
   POP.zipcode AS zipcode,
   POP.gender AS gender,
   POP.population AS zip_gender_pop,
   GEO.state_code AS state_code,
   GEO.city AS city
 FROM
   [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
 JOIN
   [bigquery-public-data:utility_us.zipcode_area] GEO
 ON
   POP.zipcode = GEO.zipcode
 WHERE
   GEO.state_code = 'NC'
 ORDER BY
   GEO.zipcode, POP.gender

And in our other example, we want to find the total population across both genders and across the multiple entries even for the same gender within a single zip code. So we have to apply an aggregator function to the population field in order to achieve that result. Since the whole point of GROUP BY is to enable rollups,
some columns are used to determine the grouping, and the remaining columns may take on different values in the rows belonging to each group. We have to end up with just one row (because we’re rolling up! — that is, we’re defining how we aggregate a lot of data points into fewer points), so an aggregation function has to be applied. The one we care about is SUM. But SQL prevents you from getting mixed up — any columns used for defining groups cannot be used as arguments in the aggregator functions.

SELECT
   GEO.state_code,
   SUM(POP.population) AS total_pop
 FROM
   [bigquery-public-data:census_bureau_usa.population_by_zip_2010] POP
 JOIN
   [bigquery-public-data:utility_us.zipcode_area] GEO
 ON
   POP.zipcode = GEO.zipcode
 GROUP BY
   GEO.state_code
 HAVING
   total_pop > 10000000