Analyse USA Name Data using BigQuery

This public dataset was created by the Social Security Administration and contains all names from Social Security card applications for births that occurred in the United States after 1879.

I used BigQuery to gain insight about American names as well as to study BigQuery.

Dataset: bigquery-public-data.usa_names.usa_1910_current

Schema:

Insight

1. Average length of names over time

    SELECT t1.year, male, female
    FROM
    (SELECT year, AVG(LENGTH(name)) as male
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE gender = 'M'
    GROUP BY year) t1
    INNER JOIN
    (SELECT year, AVG(LENGTH(name)) as female
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE gender = 'F'
    GROUP BY year) t2
    on t1.year = t2.year

2. Number of distinct name

    select count(distinct(name))
    from `bigquery-public-data.usa_names.usa_1910_current`

31595

3. 10 most common name of all time

    select name, sum(number) as count
    from `bigquery-public-data.usa_names.usa_1910_current`
    where gender = 'M'
    group by name
    order by count desc
    limit 10;

• male

• female

4. Most common name of 2018

• male

    select name, sum(number) as count
    from `bigquery-public-data.usa_names.usa_1910_current`
    where year = 2018 and gender = "M"
    group by name
    order by count desc
    limit 1;

Liam 19837

• female

    select name, sum(number) as count
    from `bigquery-public-data.usa_names.usa_1910_current`
    where year = 2018 and gender = "F"
    group by name
    order by count desc
    limit 1;

Emma 18688

5. Most common name in each decade

    WITH name_decade AS
    (SELECT 
    CASE WHEN CAST(year as STRING) like '188%' THEN '1880-1889'
        WHEN CAST(year as STRING) like '189%' THEN '1890-1899'
        WHEN CAST(year as STRING) like '190%' THEN '1900-1909'
        WHEN CAST(year as STRING) like '191%' THEN '1910-1919'
        WHEN CAST(year as STRING) like '192%' THEN '1920-1929'
        WHEN CAST(year as STRING) like '193%' THEN '1930-1939'
        WHEN CAST(year as STRING) like '194%' THEN '1940-1949'
        WHEN CAST(year as STRING) like '195%' THEN '1950-1959'
        WHEN CAST(year as STRING) like '196%' THEN '1960-1969'
        WHEN CAST(year as STRING) like '197%' THEN '1970-1979'
        WHEN CAST(year as STRING) like '198%' THEN '1980-1989'
        WHEN CAST(year as STRING) like '199%' THEN '1990-1999'
        WHEN CAST(year as STRING) like '200%' THEN '2000-2009'
        WHEN CAST(year as STRING) like '201%' THEN '2010-2019'
    END AS decade,
    Name, SUM(number) AS count
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE Gender = 'F'
    GROUP BY decade, Name)

    SELECT t1.decade, t1.name, t1.count
    FROM name_decade t1
    INNER JOIN 
    (SELECT decade, MAX(count) as max_count from name_decade group by decade) t2
    ON t1.decade = t2.decade AND t1.count = t2.max_count

• female

• male

6. Most female common name in each state 2008 - 2018

    WITH state_name AS
    (SELECT state, name, sum(number) AS count
    FROM `bigquery-public-data.usa_names.usa_1910_curren`
    WHERE gender = 'F' AND year >= 2008
    GROUP BY state, name)

    SELECT t1.state, t1.name, t1.count
    FROM state_name t1
    INNER JOIN
    (SELECT state, max(count) as max_count
    FROM state_name
    GROUP BY state) t2
    ON t1.state = t2.state AND t1.count = t2.max_count
    ORDER BY t1.state

7. Top 10 gender neutral names

    WITH name_gender AS
    (
    SELECT name, SUM(IF(gender='F',number,0)) female, SUM(IF(gender='M',number,0)) male
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    GROUP BY name
    )

    SELECT name, ABS(male-female)/(male+female) balance, female, male
    FROM name_gender
    WHERE male * female>1000000
    ORDER BY 2
    LIMIT 10;

8. Most common name of each first letter 2008-2018

    WITH first_letter_name AS
    (
    SELECT SUBSTR(name, 0, 1) AS letter, name, SUM(number) AS count
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE year >= 2008 and gender = 'F'
    GROUP BY letter, name
    ORDER BY letter, count DESC
    )
    SELECT letter, name, rank
    FROM 
    (SELECT letter, name, count,
    Rank() over (Partition BY letter
                    ORDER BY count DESC ) AS rank
    FROM first_letter_name) t
    WHERE rank <= 10

Some interesting insights:

• American have around 30000 ways to name their children.
• American female's names tend to be longer than male's names.
• Most common American name: James for male and Mary for female.
• Most common American name in 2018: Liam for male and Emma for female.
• In 5 continuous decades, 1910s -> 1950s, the most common female name is Mary.
  Mary is both a traditional American name and a symbol religious Christianity.
  But in recent year, Mary is no longer a favourite name. According to sociologist Stanley Lieberson, the reason is "As the role of the extended family, religious rules, and other institutional pressures declines," he wrote, "choices are increasingly free to be matters of taste."
• The most gender-neutral name is: Santana.