Skip to content

Instantly share code, notes, and snippets.

@tswast
Created April 4, 2017 19:49
Show Gist options
  • Save tswast/c840593e81826549efe6fd962c220eb2 to your computer and use it in GitHub Desktop.
Save tswast/c840593e81826549efe6fd962c220eb2 to your computer and use it in GitHub Desktop.
Typical USA Names by State
#standardSQL
SELECT
a.name AS name,
a.state AS state,
a.gender AS gender,
a.year AS year,
a.number AS number,
a.name_frequency AS name_frequency
FROM
`usa_names.names_conditional_probabilities` a
JOIN (
SELECT
state,
gender,
year,
MIN(ABS(name_frequency-0.5)) AS min_median
FROM
`usa_names.names_conditional_probabilities`
WHERE
number > 12
GROUP BY
state,
gender,
year ) b
ON
a.state = b.state
AND a.gender = b.gender
AND a.year = b.year
WHERE
a.number > 12
AND ABS(a.name_frequency-0.5) = b.min_median
ORDER BY
year,
state,
gender,
name DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment