USA Names Conditional Probabilities

usa-names-conditional-probabilities.sql

#standardSQL
SELECT
  a.name AS name,
  a.state AS state,
  a.gender AS gender,
  a.year AS year,
  a.number AS number,
  (a.number / b.total_number) AS name_frequency
FROM
  `bigquery-public-data.usa_names.usa_1910_current` a
JOIN (
  SELECT
    name,
    gender,
    year,
    SUM(number) AS total_number
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  GROUP BY
    name,
    gender,
    year) b
ON
  a.name = b.name
  AND a.gender = b.gender
  AND a.year = b.year

Why is name frequency equal to “number / total number”? The name frequency values represent conditional probabilities, for example

P(State=OH|Name=Pearl & Gender=M & Year=1917) =
    P(State=OH & Name=Pearl & Gender=M & Year=1917) /
    P(Name=Pearl & Gender=M & Year=1917)