Typical USA Names by State

typical-usa-names-by-state.sql

#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