SQL Practice - Baby Names

Using the handy query() function of the notebook Visnu Pitiyanuvath built up to query Social Security Administration's baby names data from BigQuery. I haven't been writing SQL lately, and needed to brush up on my fluency. I'm also curious to see how authoring sql queries works out in Observable.

There are some important things that you're supposed to keep in mind with the SSA data that I'll ignore:

  1. Names with fewer than 5 births in a state in a year are excluded, so none of the totals are accurate
  2. The names and genders provided to the SSA are not a perfect representation of the actual names and genders of the people in the real world we're interested in understanding.
  3. There are meaningful groupings of names beyond exact string matches. In the current form, the data treats the alternate spellings like "Michelle" and "Michele" as different names, as different as "Michelle" and "Benedict"
import { [query](/content/@visnup/baby-names-by-birth-year#query/index.html)} from ["@visnup/baby-names-by-birth-year"](/content/@visnup/baby-names-by-birth-year/index.html)
import { [printTable](/content/@uwdata/data-utilities#printTable/index.html)} from ["@uwdata/data-utilities"](/content/@uwdata/data-utilities/index.html)

// Query data
const table = "`bigquery-public-data.usa_names.usa_1910_current`"
const q = "select count(*) as cnt from `bigquery-public-data.usa_names.usa_1910_current`"

What names saw the biggest one year drop-off?

  1. Aggregate by state level to countrywide (missed this step at first)
  2. Use a window to get the lag of number,
  3. Calc absolute and % drop
  4. Get top 10 by gender

Analytic function concepts OVER (PARTITION BY division ORDER BY finish_time ASC)

Possible iterations:

  1. Names with biggest 5 year drop.
  2. Names with biggest 1 year rise

Other good query questions:

  1. What name was most popular with both genders in the most recent year of data? (define "most popular with both genders meaningfully")
  2. Is the percentage of girls names ending in vowels increasing or decreasing over the last 30 years?
q1 = `
WITH group_by_gender AS (
  SELECT gender, year, name, SUM(number) AS number
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  GROUP BY gender, year, name
)
...
SELECT * FROM
  (SELECT *,
          LAG(number) OVER w AS lag_number,
          number - LAG(number) OVER w AS net_change_yoy,
          -1 + number / LAG(number) OVER w AS yoy_pct_change
   FROM group_by_gender
   WINDOW w AS (PARTITION BY gender,name ORDER BY year)) q
WHERE net_change_yoy IS NOT NULL
AND year >= 1980 AND year <= 1989
AND number >= 1000
ORDER BY yoy_pct_change
LIMIT 50
`

Is the percentage of girls names ending in vowels increasing or decreasing over the last 50 years?

  1. Filter on girls, last 30 years...changed to 50.
  2. New field ends_in_vowel
  3. Group by year
  4. Aggregate mean(ends_in_vowel)
q2 = `
WITH q_filter AS (
  SELECT *,
    REGEXP_CONTAINS(name, r'[aeiouy]$') AS ends_with_vowel
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE gender = 'F' AND year >= EXTRACT(year FROM current_date()) - 50
)
SELECT year, SUM(CAST(ends_with_vowel AS int64) * number) / SUM(number) AS pct_ends_with_e
FROM q_filter
GROUP BY year
ORDER BY year DESC
`

Names that are popular with both genders

I think a useful metric is the max of the ranks by gender. So the we'll find:

  1. The most popular names for boys that are more popular for girls
  2. The most popular names for girls that are more popular for boys

Not sure converting to ranks will change things much, but it'll facilitate comparison across decades and other groupings where the number of people born is different in each grouping.

q3 = `
WITH
q AS (
  SELECT FLOOR(year/10)*10 AS decade, name, gender, SUM(number) AS number
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  GROUP BY decade, name, gender
),
w_rank AS (
  SELECT *,
         RANK() OVER (PARTITION BY CAST(decade AS STRING), gender ORDER BY number DESC) AS rank
  FROM q
),

boys AS (SELECT * FROM w_rank WHERE gender = 'M'),
girls AS (SELECT * FROM w_rank WHERE gender = 'F')
SELECT
  boys.decade,
  boys.name AS name,
  boys.rank AS rank_boy,
  girls.rank AS rank_girl,
  girls.rank + boys.rank AS sum_rank
FROM boys
INNER JOIN girls ON boys.name = girls.name AND boys.decade = girls.decade
ORDER BY sum_rank
LIMIT 100
`