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:

Field name Type Mode Description
state STRING NULLABLE 2-digit state code
gender STRING NULLABLE Sex (M=male or F=female)
year INTEGER NULLABLE 4-digit year of birth
name STRING NULLABLE Given name of a person at birth
number INTEGER NULLABLE Number of occurrences of the name

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

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

31595

  1. 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
name count
James 4997327
John 4869607
Robert 4734038
Michael 4349307
William 3890923
David 3597725
Richard 2539873
Joseph 2522812
Charles 2273068
Thomas 2245124
  • female
name count
Mary 3741196
Patricia 1569022
Elizabeth 1537684
Jennifer 1466161
Linda 1447943
Barbara 1424221
Margaret 1130920
Susan 1109309
Dorothy 1053390
Jessica 1043442
  1. 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

  1. 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
decade name count
1910-1919 Mary 478639
1920-1929 Mary 701754
1930-1939 Mary 572956
1940-1949 Mary 640031
1950-1959 Mary 625568
1960-1969 Lisa 496976
1970-1979 Jennifer 581763
1980-1989 Jessica 469487
1990-1999 Jessica 303094
2000-2009 Emily 223690
2010-2019 Emma 177410
  • male
decade name count
1910-1919 John 376318
1920-1929 Robert 576363
1930-1939 Robert 590733
1940-1949 James 795680
1950-1959 James 843531
1960-1969 Michael 833216
1970-1979 Michael 707645
1980-1989 Michael 663741
1990-1999 Michael 462327
2000-2009 Jacob 273844
2010-2019 Noah 163657
  1. 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
Result
state name count
AK Emma 552
AL Emma 3206
AR Emma 2095
AZ Sophia 5249
CA Sophia 32754
CO Olivia 3456
CT Olivia 2504
DC Olivia 455
DE Ava 656
FL Isabella 16462
GA Ava 6204
HI Sophia 624
IA Emma 2203
ID Emma 1249
IL Olivia 8976
IN Emma 4980
KS Emma 2234
KY Emma 3518
LA Ava 3211
MA Olivia 4838
MD Olivia 3333
ME Emma 941
MI Olivia 6462
MN Olivia 3940
MO Emma 4465
MS Ava 1898
MT Emma 682
NC Emma 6688
ND Emma 717
NE Emma 1412
NH Olivia 955
NJ Isabella 6505
NM Isabella 1283
NV Sophia 2023
NY Sophia 13482
OH Emma 8315
OK Emma 2825
OR Emma 2549
PA Emma 8849
RI Sophia 831
SC Emma 2825
SD Emma 661
TN Emma 5079
TX Isabella 21531
UT Olivia 2947
VA Emma 5256
VT Emma 378
WA Olivia 4789
WI Olivia 3759
WV Emma 1448
WY Emma 369
  1. 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;
name balance female male
Santana 9.445843828715365E-4 3179 3173
Landry 9.946949602122016E-4 3013 3019
Lennon 0.0041753653444676405 3367 3339
Kris 0.006668493651228647 11020 10874
Kerry 0.007894221777613467 45620 46346
Justice 0.012610626641164456 15229 15618
Arden 0.015634580012262415 3211 3313
Quinn 0.01792151435942118 29791 28742
Oakley 0.025977733371395945 3412 3594
  1. 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
Result for male
letter name rank
A Alexander 1
A Aiden 2
A Anthony 3
A Andrew 4
A Aaron 5
A Angel 6
A Adrian 7
A Austin 8
A Adam 9
A Ayden 10
B Benjamin 1
B Brayden 2
B Brandon 3
B Blake 4
B Brody 5
B Bentley 6
B Bryson 7
B Bryan 8
B Bryce 8
B Brian 10
C Christopher 1
C Christian 2
C Caleb 3
C Carter 4
C Connor 5
C Charles 6
C Cameron 7
C Colton 8
C Chase 9
C Cooper 10
D Daniel 1
D David 2
D Dylan 3
D Dominic 4
D Diego 5
D Damian 6
D Declan 7
D Devin 8
D Derek 9
D Damien 10
E Ethan 1
E Elijah 2
E Evan 3
E Eli 4
E Easton 5
E Elias 6
E Eric 7
E Ezra 8
E Edward 9
E Ezekiel 10
F Francisco 1
F Fernando 2
F Finn 3
F Felix 4
F Fabian 5
F Frank 6
F Finley 7
F Finnegan 8
F Franklin 9
F Frederick 10
G Gabriel 1
G Gavin 2
G Grayson 3
G Giovanni 4
G Greyson 5
G George 6
G Grant 7
G Gage 8
G Gael 9
G Graham 10
H Henry 1
H Hunter 2
H Hudson 3
H Hayden 4
H Harrison 5
H Hector 6
H Holden 7
H Hugo 8
H Hendrix 9
H Hayes 10
I Isaac 1
I Isaiah 2
I Ian 3
I Ivan 4
I Israel 5
I Iker 6
I Ismael 7
I Izaiah 8
I Ibrahim 9
I Issac 10
J Jacob 1
J James 2
J Jayden 3
J Joseph 4
J Joshua 5
J Jackson 6
J John 7
J Jonathan 8
J Jack 9
J Julian 10
K Kevin 1
K Kayden 2
K Kaleb 3
K Kyle 4
K Kaden 5
K Kaiden 6
K Kai 7
K Kingston 8
K Kenneth 9
K King 10
L Liam 1
L Logan 2
L Lucas 3
L Luke 4
L Landon 5
L Levi 6
L Luis 7
L Lincoln 8
L Leo 9
L Leonardo 10
M Michael 1
M Mason 2
M Matthew 3
M Mateo 4
M Micah 5
M Max 6
M Miles 7
M Maxwell 8
M Miguel 9
M Marcus 10
N Noah 1
N Nathan 2
N Nicholas 3
N Nolan 4
N Nathaniel 5
N Nicolas 6
N Nehemiah 7
N Nash 8
N Noel 9
N Nasir 10
O Owen 1
O Oliver 2
O Oscar 3
O Omar 4
O Orion 5
O Orlando 6
O Odin 7
O Omari 8
O Otto 9
O Oakley 10
P Parker 1
P Preston 2
P Patrick 3
P Paul 4
P Peter 5
P Peyton 6
P Paxton 7
P Pedro 8
P Phoenix 9
P Phillip 10
Q Quinn 1
Q Quentin 2
Q Quinton 3
Q Quincy 4
Q Quintin 5
Q Quinten 6
Q Quadir 7
Q Quinlan 8
Q Quran 9
Q Qasim 10
R Ryan 1
R Robert 2
R Ryder 3
R Roman 4
R Richard 5
R Riley 6
R Ryker 7
R Rylan 8
R Ricardo 9
R Rowan 10
S Samuel 1
S Sebastian 2
S Santiago 3
S Sawyer 4
S Steven 5
S Sean 6
S Silas 7
S Seth 8
S Stephen 9
S Spencer 10
T Tyler 1
T Thomas 2
T Tristan 3
T Theodore 4
T Timothy 5
T Tucker 6
T Tanner 7
T Trevor 8
T Travis 9
T Trenton 10
U Uriel 1
U Uriah 2
U Ulises 3
U Urijah 4
U Ulysses 5
U Unknown 6
U Umar 7
U Uziel 8
U Usher 9
U Ulisses 10
V Vincent 1
V Victor 2
V Vicente 3
V Valentino 4
V Vihaan 5
V Vincenzo 6
V Vance 7
V Van 8
V Valentin 9
V Vaughn 10
W William 1
W Wyatt 2
W Wesley 3
W Weston 4
W Waylon 5
W Walter 6
W Walker 7
W Warren 8
W Wade 9
W Winston 10
X Xavier 1
X Xander 2
X Xzavier 3
X Xavi 4
X Xavion 5
X Xavian 6
X Xaiden 7
X Xavior 8
X Xzavion 9
X Xayden 9
Y Yahir 1
Y Yusuf 2
Y Yosef 3
Y Yousef 4
Y Yehuda 5
Y Yandel 6
Y Yisroel 7
Y Yadiel 8
Y Yael 9
Y Yaakov 10
Z Zachary 1
Z Zane 2
Z Zion 3
Z Zayden 4
Z Zander 5
Z Zachariah 6
Z Zaiden 7
Z Zayne 8
Z Zackary 9
Z Zayn 10
Result for female
letter name rank
A Ava 1
A Abigail 2
A Amelia 3
A Addison 4
A Avery 5
A Aubrey 6
A Anna 7
A Alexis 8
A Allison 9
A Audrey 10
B Brooklyn 1
B Brianna 2
B Bella 3
B Bailey 4
B Brooke 5
B Brielle 6
B Brooklynn 7
B Brynn 8
B Briana 9
B Bianca 10
C Chloe 1
C Charlotte 2
C Camila 3
C Claire 4
C Caroline 5
C Clara 6
C Cora 7
C Catherine 8
C Cecilia 9
C Callie 10
D Destiny 1
D Delilah 2
D Daisy 3
D Daniela 4
D Diana 5
D Danielle 6
D Delaney 7
D Dakota 8
D Daniella 9
D Daphne 10
E Emma 1
E Emily 2
E Elizabeth 3
E Ella 4
E Evelyn 5
E Ellie 6
E Eva 7
E Eleanor 8
E Elena 9
E Eliana 10
F Faith 1
F Fiona 2
F Finley 3
F Fatima 4
F Fernanda 5
F Francesca 6
F Felicity 7
F Freya 8
F Frances 9
F Farrah 10
G Grace 1
G Gabriella 2
G Genesis 3
G Gianna 4
G Gabrielle 5
G Gracie 6
G Giselle 7
G Gabriela 8
G Genevieve 9
G Georgia 10
H Harper 1
H Hannah 2
H Hailey 3
H Hazel 4
H Hadley 5
H Hayden 6
H Haley 7
H Harmony 8
H Hope 9
H Heaven 10
I Isabella 1
I Isabelle 2
I Isabel 3
I Ivy 4
I Isla 5
I Izabella 6
I Iris 7
I Itzel 8
I Imani 9
I Irene 10
J Julia 1
J Jasmine 2
J Jocelyn 3
J Jade 4
J Jordyn 5
J Jessica 6
J Josephine 7
J Juliana 8
J Jennifer 9
J Jayla 10
K Kaylee 1
K Kylie 2
K Katherine 3
K Kayla 4
K Kennedy 5
K Khloe 6
K Kimberly 7
K Kaitlyn 8
K Kinsley 9
K Kendall 10
L Lily 1
L Lillian 2
L Layla 3
L Leah 4
L Lucy 5
L Lauren 6
L Lydia 7
L London 8
L Liliana 9
L Luna 10
M Mia 1
M Madison 2
M Madelyn 3
M Maya 4
M Mackenzie 5
M Madeline 6
M Mila 7
M Makayla 8
M Melanie 9
M Morgan 10
N Natalie 1
N Nevaeh 2
N Nora 3
N Naomi 4
N Natalia 5
N Nicole 6
N Norah 7
N Nova 8
N Nadia 9
N Noelle 10
O Olivia 1
O Olive 2
O Oakley 3
O Ophelia 4
O Octavia 5
O Opal 6
O Oaklyn 7
O Oaklee 8
O Oaklynn 9
O Oakleigh 10
P Peyton 1
P Penelope 2
P Paisley 3
P Piper 4
P Payton 5
P Paige 6
P Presley 7
P Parker 8
P Phoebe 9
P Paris 10
Q Quinn 1
Q Queen 2
Q Quincy 3
Q Queenie 4
Q Quetzalli 5
Q Queena 6
Q Quetzaly 7
Q Quetzali 8
Q Quincey 9
Q Quincee 10
R Riley 1
R Ruby 2
R Rylee 3
R Reagan 4
R Rachel 5
R Reese 6
R Rebecca 7
R Ryleigh 8
R Rose 9
R Raelynn 10
S Sophia 1
S Sofia 2
S Samantha 3
S Sarah 4
S Savannah 5
S Scarlett 6
S Stella 7
S Serenity 8
S Sophie 9
S Sadie 10
T Taylor 1
T Trinity 2
T Tessa 3
T Teagan 4
T Talia 5
T Tatum 6
T Tiffany 7
T Tatiana 8
T Tiana 9
T Thea 10
U Unique 1
U Unknown 2
U Uma 3
U Una 4
U Ursula 5
U Udy 6
U Uriah 7
U Unity 8
U Ulyana 9
U Umme 10
U Umaiza 10
V Victoria 1
V Violet 2
V Vivian 3
V Valeria 4
V Valentina 5
V Vanessa 6
V Valerie 7
V Vivienne 8
V Veronica 9
V Vera 10
W Willow 1
W Willa 2
W Whitney 3
W Winter 4
W Wendy 5
W Wren 6
W Wynter 7
W Whitley 8
W Winnie 9
W Waverly 10
X Ximena 1
X Xiomara 2
X Xochitl 3
X Xitlali 4
X Xitlaly 5
X Xena 6
X Xenia 7
X Xyla 8
X Xochilt 9
X Ximenna 10
Y Yaretzi 1
Y Yareli 2
Y Yasmin 3
Y Yamileth 4
Y Yaritza 5
Y Yesenia 6
Y Yazmin 7
Y Yoselin 8
Y Yuliana 9
Y Yara 10
Z Zoey 1
Z Zoe 2
Z Zara 3
Z Zariah 4
Z Zuri 5
Z Zoie 6
Z Zaniyah 7
Z Zaria 8
Z Zion 9
Z Zariyah 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.