Ram Rallabandi

Project 11: Analyzing American baby name trends over 100 years

Published by

Ram Rallabandi

on

1. Classic American names

Lots of name tags piled on top of each other.

Photo by Travis Wise on Wikimedia.

How have American baby name tastes changed since 1920? Which names have remained popular for over 100 years, and how do those names compare to more recent top baby names? These are considerations for many new parents, but the skills we’ll practice while answering these queries are broadly applicable. After all, understanding trends and popularity is important for many businesses, too!

We’ll be working with data provided by the United States Social Security Administration, which lists first names along with the number and sex of babies they were given to in each year. For processing speed purposes, we’ve limited the dataset to first names which were given to over 5,000 American babies in a given year. Our data spans 101 years, from 1920 through 2020.

baby_names

columntypemeaning
yearintyear
first_namevarcharfirst name
sexvarcharsex of babies given first_name
numintnumber of babies of sex given first_name in that year

Let’s get oriented to American baby name tastes by looking at the names that have stood the test of time!

In [12]:

%%sql
postgresql:///names
    
-- Select first names and the total babies with that first_name
-- Group by first_name and filter for those names that appear in all 101 years
-- Order by the total number of babies with that first_name, descending

SELECT first_name, SUM(num) FROM baby_names
GROUP BY first_name
HAVING COUNT(year)=101
ORDER BY sum DESC

8 rows affected.

Out[12]:

first_namesum
James4748138
John4510721
William3614424
David3571498
Joseph2361382
Thomas2166802
Charles2112352
Elizabeth1436286

2. Timeless or trendy?

Wow, it looks like there are a lot of timeless traditionally male names! Elizabeth is holding her own for the female names, too.

Now, let’s broaden our understanding of the dataset by looking at all names. We’ll attempt to capture the type of popularity that each name in the dataset enjoyed. Was the name classic and popular across many years or trendy, only popular for a few years? Let’s find out.

In [14]:

%%sql

-- Classify first names as 'Classic', 'Semi-classic', 'Semi-trendy', or 'Trendy'
-- Alias this column as popularity_type
-- Select first_name, the sum of babies who have ever had that name, and popularity_type
-- Order the results alphabetically by first_name

SELECT first_name, SUM(num),
CASE
    WHEN COUNT(year) > 80 THEN 'Classic'
    WHEN COUNT(year) > 50 THEN 'Semi-classic'
    WHEN COUNT(year) > 20 THEN 'Semi-Trendy'
    ELSE 'Trendy' 
END AS popularity_type
FROM baby_names
GROUP BY first_name
ORDER BY first_name

 * postgresql:///names
547 rows affected.

Out[14]:

first_namesumpopularity_type
Aaliyah15870Trendy
Aaron530592Semi-classic
Abigail338485Semi-Trendy
Adam497293Semi-Trendy
Addison107433Trendy
Adrian147741Semi-Trendy
Aidan68566Trendy
Aiden216194Trendy
Alan162041Semi-Trendy
Albert260945Semi-Trendy
Alex158677Semi-Trendy
Alexa33522Trendy
Alexander579854Semi-Trendy
Alexandra167122Semi-Trendy
Alexandria5026Trendy
Alexis282149Semi-Trendy
Alfred16260Trendy
Alice296559Semi-Trendy
Alicia84579Trendy
Allen10256Trendy
Allison214995Semi-Trendy
Alyssa269134Semi-Trendy
Amanda699911Semi-Trendy
Amber313418Semi-Trendy
Amelia106381Trendy
Amy569542Semi-Trendy
Andrea321655Semi-Trendy
Andrew1157548Semi-classic
Angel157667Trendy
Angela541553Semi-Trendy
Angelina11337Trendy
Anita44692Trendy
Ann336091Semi-Trendy
Anna445496Semi-classic
Anne70228Trendy
Annette49954Trendy
Annie95837Trendy
Anthony1344352Classic
Antonio10097Trendy
April138714Trendy
Aria52145Trendy
Ariana5497Trendy
Arianna5270Trendy
Ariel5410Trendy
Arthur309705Semi-Trendy
Asher38156Trendy
Ashley798738Semi-Trendy
Ashton5436Trendy
Aubrey72220Trendy
Audrey48341Trendy
Aurora5184Trendy
Austin365295Semi-Trendy
Ava265126Trendy
Avery112293Trendy
Ayden34244Trendy
Bailey10219Trendy
Barbara1343901Semi-classic
Barry85434Trendy
Beatrice27983Trendy
Bella5127Trendy
Benjamin627696Semi-Trendy
Bentley16844Trendy
Bernice46347Trendy
Beth55228Trendy
Betty893396Semi-Trendy
Beverly310683Semi-Trendy
Billy270759Semi-Trendy
Blake48795Trendy
Bobby203289Semi-Trendy
Bonnie193352Semi-Trendy
Bradley147275Semi-Trendy
Brandi16199Trendy
Brandon729832Semi-Trendy
Brandy48762Trendy
Brayden93754Trendy
Brenda513283Semi-Trendy
Brian1107302Semi-classic
Briana5001Trendy
Brianna210328Semi-Trendy
Brittany326255Trendy
Brittney37878Trendy
Brody21815Trendy
Brooke110847Trendy
Brooklyn62260Trendy
Bruce266549Semi-Trendy
Bryan314250Semi-Trendy
Caden5052Trendy
Caitlin38501Trendy
Caleb259439Semi-Trendy
Cameron253711Semi-Trendy
Camila51882Trendy
Carl334800Semi-Trendy
Carla22276Trendy
Carlos118325Trendy
Carol740607Semi-Trendy
Carole21186Trendy
Caroline5021Trendy
Carolyn438382Semi-Trendy
Carrie77899Trendy
Carson25598Trendy
Carter141274Trendy
Cassandra42677Trendy
Catherine345852Semi-Trendy
Cathy119020Trendy
Chad177923Trendy
Charles2112352Classic
Charlotte141540Trendy
Chase97684Trendy
Chelsea100857Trendy
Cheryl392691Semi-Trendy
Chloe187720Semi-Trendy
Chris48878Trendy
Christian357457Semi-Trendy
Christina366859Semi-Trendy
Christine465653Semi-Trendy
Christopher2012792Semi-classic
Christy10235Trendy
Cindy161070Trendy
Claire10225Trendy
Clara21336Trendy
Clarence77134Trendy
Cody225952Semi-Trendy
Cole72461Trendy
Colin5122Trendy
Colton58377Trendy
Connie179476Semi-Trendy
Connor203106Semi-Trendy
Cooper31011Trendy
Corey70531Trendy
Cory29454Trendy
Courtney202829Semi-Trendy
Craig190323Semi-Trendy
Crystal239999Semi-Trendy
Curtis48098Trendy
Cynthia577859Semi-Trendy
Dakota34334Trendy
Dale130919Trendy
Dana51558Trendy
Daniel1824274Classic
Danielle299683Semi-Trendy
Danny161078Trendy
Darlene89561Trendy
Darren5935Trendy
Darryl10142Trendy
David3571498Classic
Dawn225877Semi-Trendy
Debbie138846Trendy
Deborah675049Semi-Trendy
Debra508230Semi-Trendy
Denise285039Semi-Trendy
Dennis492221Semi-Trendy
Derek105026Trendy
Destiny100465Trendy
Devin70280Trendy
Diana172195Semi-Trendy
Diane453135Semi-Trendy
Diego46535Trendy
Dillon5062Trendy
Dolores101453Trendy
Dominic67420Trendy
Donald1280236Semi-classic
Donna762594Semi-Trendy
Doris336062Semi-Trendy
Dorothy791084Semi-Trendy
Douglas426439Semi-Trendy
Dustin138651Trendy
Dylan360776Semi-Trendy
Earl74214Trendy
Easton21820Trendy
Edith53687Trendy
Edna63698Trendy
Edward1013143Semi-classic
Elaine100359Trendy
Eleanor119863Trendy
Eli74938Trendy
Elias16965Trendy
Elijah277457Semi-Trendy
Elizabeth1436286Classic
Ella154079Trendy
Ellen82403Trendy
Ellie26266Trendy
Emily750420Semi-Trendy
Emma448087Semi-Trendy
Eric797880Semi-classic
Erica156158Trendy
Erin239718Semi-Trendy
Ernest43959Trendy
Esther28040Trendy
Ethan408918Semi-Trendy
Ethel53359Trendy
Eugene239512Semi-Trendy
Evan203165Semi-Trendy
Evelyn310824Semi-Trendy
Ezekiel5013Trendy
Ezra24632Trendy
Faith27204Trendy
Florence99171Trendy
Frances348520Semi-Trendy
Francis59097Trendy
Frank596887Semi-classic
Franklin5364Trendy
Fred170837Semi-Trendy
Gabriel270207Semi-Trendy
Gabriella51486Trendy
Gabrielle51144Trendy
Gail118334Trendy
Garrett15976Trendy
Gary817491Semi-Trendy
Gavin130460Trendy
George1032513Semi-classic
Gerald327545Semi-Trendy
Geraldine52850Trendy
Gertrude16229Trendy
Gianna7826Trendy
Gina46380Trendy
Gladys77627Trendy
Glenn94437Trendy
Gloria331698Semi-Trendy
Grace254573Semi-Trendy
Grayson61689Trendy
Greg15849Trendy
Gregory644286Semi-Trendy
Hailey111571Trendy
Haley106119Trendy
Hannah392284Semi-Trendy
Harold368463Semi-Trendy
Harper86554Trendy
Harry182312Semi-Trendy
Hayden34724Trendy
Hazel66103Trendy
Heather468165Semi-Trendy
Helen569998Semi-Trendy
Henry429656Semi-classic
Herbert52341Trendy
Holly54634Trendy
Howard157144Semi-Trendy
Hudson43048Trendy
Hunter220439Semi-Trendy
Ian159100Semi-Trendy
Irene110116Trendy
Isaac209563Semi-Trendy
Isabella336924Semi-Trendy
Isaiah200116Semi-Trendy
Jace23233Trendy
Jack552411Semi-classic
Jackson219588Semi-Trendy
Jacob888209Semi-Trendy
Jacqueline223092Semi-Trendy
Jaden21777Trendy
Jaime13744Trendy
James4748138Classic
Jamie157417Trendy
Jane195627Semi-Trendy
Janet444842Semi-Trendy
Janice328335Semi-Trendy
Jared137848Semi-Trendy
Jasmine192464Semi-Trendy
Jason998257Semi-Trendy
Jaxon66542Trendy
Jaxson5061Trendy
Jay20682Trendy
Jayden213005Trendy
Jean363812Semi-Trendy
Jeff61390Trendy
Jeffery131241Trendy
Jeffrey907378Semi-Trendy
Jenna60548Trendy
Jennifer1404743Semi-Trendy
Jeremiah104792Trendy
Jeremy366271Semi-Trendy
Jerry494469Semi-Trendy
Jesse183645Semi-Trendy
Jessica994210Semi-Trendy
Jesus82359Trendy
Jill120405Trendy
Jim16588Trendy
Jimmy135558Semi-Trendy
Jo84238Trendy
Joan413286Semi-Trendy
Joanne77323Trendy
Jocelyn5292Trendy
Joe302127Semi-Trendy
John4510721Classic
Johnny138737Semi-Trendy
Jonathan772846Semi-classic
Jordan426912Semi-Trendy
Jose434805Semi-Trendy
Joseph2361382Classic
Josephine69222Trendy
Joshua1204236Semi-Trendy
Josiah74001Trendy
Joyce436267Semi-Trendy
Juan189094Semi-Trendy
Juanita20758Trendy
Judith377449Semi-Trendy
Judy329356Semi-Trendy
Julia112397Trendy
Julian137742Trendy
Julie411989Semi-Trendy
June61668Trendy
Justin729931Semi-Trendy
Kaitlyn121541Trendy
Karen892033Semi-Trendy
Katelyn65023Trendy
Katherine413349Semi-classic
Kathleen516918Semi-Trendy
Kathryn204173Semi-Trendy
Kathy269922Semi-Trendy
Katie97378Trendy
Kayla294192Semi-Trendy
Kaylee65209Trendy
Keith313978Semi-Trendy
Kelly417352Semi-Trendy
Kelsey95434Trendy
Kenneth1153846Semi-classic
Kevin1140092Semi-classic
Khloe5406Trendy
Kim143648Trendy
Kimberly767543Semi-Trendy
Kristen127223Trendy
Kristin81495Trendy
Kristina10585Trendy
Kristy5331Trendy
Krystal5935Trendy
Kyle394877Semi-Trendy
Kylie16309Trendy
Landon129558Trendy
Larry700521Semi-Trendy
Latoya5051Trendy
Laura587161Semi-Trendy
Lauren401513Semi-Trendy
Laurie87568Trendy
Lawrence307238Semi-Trendy
Layla74474Trendy
Leah63600Trendy
Leo32643Trendy
Leonard54127Trendy
Leslie73075Trendy
Levi91814Trendy
Liam213059Trendy
Lillian185120Semi-Trendy
Lily115354Trendy
Lincoln43147Trendy
Linda1361021Semi-Trendy
Lindsay69178Trendy
Lindsey88669Trendy
Lisa920119Semi-Trendy
Logan316927Semi-Trendy
Lois220781Semi-Trendy
Lori289439Semi-Trendy
Lorraine27338Trendy
Louis115731Trendy
Louise106456Trendy
Lucas191033Trendy
Lucille59379Trendy
Luis144176Semi-Trendy
Luke207795Semi-Trendy
Luna27822Trendy
Lynn97059Trendy
Mackenzie46972Trendy
Madeline26888Trendy
Madison378127Semi-Trendy
Makayla55446Trendy
Marc5013Trendy
Marcia15571Trendy
Marcus53788Trendy
Margaret806838Semi-Trendy
Maria417502Semi-classic
Mariah15723Trendy
Marie249462Semi-Trendy
Marilyn286722Semi-Trendy
Marion50545Trendy
Marissa29003Trendy
Marjorie114386Trendy
Mark1265910Semi-classic
Marlene10368Trendy
Marsha21303Trendy
Martha359762Semi-Trendy
Martin56023Trendy
Mary3215850Classic
Mason263609Semi-Trendy
Mateo45440Trendy
Matthew1567204Semi-classic
Maverick16863Trendy
Maya5047Trendy
Megan384668Semi-Trendy
Melanie43995Trendy
Melissa666250Semi-Trendy
Mia216167Trendy
Michael4278824Classic
Michele139690Trendy
Michelle736097Semi-Trendy
Mike97902Trendy
Mila28047Trendy
Mildred195666Trendy
Miles5249Trendy
Miranda11434Trendy
Misty34935Trendy
Mitchell5370Trendy
Monica111143Trendy
Morgan157320Trendy
Nancy854761Semi-Trendy
Natalie266634Semi-Trendy
Nathan493746Semi-Trendy
Nathaniel103671Trendy
Nevaeh42926Trendy
Nicholas777269Semi-Trendy
Nicole533803Semi-Trendy
Noah389490Semi-Trendy
Nolan38147Trendy
Nora34285Trendy
Norma144522Semi-Trendy
Norman47596Trendy
Oliver107511Trendy
Olivia429118Semi-Trendy
Owen151569Trendy
Paige48894Trendy
Paisley5085Trendy
Pamela524481Semi-Trendy
Parker27453Trendy
Patricia1479802Semi-classic
Patrick559661Semi-classic
Paul1218996Semi-classic
Paula196090Semi-Trendy
Pauline64073Trendy
Peggy220586Semi-Trendy
Penelope43409Trendy
Penny10128Trendy
Peter388795Semi-classic
Peyton5315Trendy
Philip100415Trendy
Phillip86811Trendy
Phyllis251517Semi-Trendy
Rachel434626Semi-Trendy
Ralph273663Semi-Trendy
Randall89055Trendy
Randy215094Trendy
Raymond541922Semi-classic
Rebecca638458Semi-classic
Regina10003Trendy
Renee61185Trendy
Rhonda157706Trendy
Richard2414838Classic
Rick5462Trendy
Ricky119547Trendy
Riley73607Trendy
Rita125877Semi-Trendy
Robert4495199Classic
Robin210806Trendy
Rodney125500Trendy
Roger314531Semi-Trendy
Ronald974343Semi-classic
Ronnie45564Trendy
Rose248527Semi-Trendy
Roy227920Semi-Trendy
Ruby93528Trendy
Russell128647Semi-Trendy
Ruth475908Semi-Trendy
Ryan926995Semi-Trendy
Sabrina11589Trendy
Sally30713Trendy
Samantha514826Semi-Trendy
Samuel539556Semi-classic
Sandra783878Semi-Trendy
Santiago5036Trendy
Sara226696Semi-Trendy
Sarah777519Semi-Trendy
Savannah134405Semi-Trendy
Scarlett54329Trendy
Scott704468Semi-Trendy
Sean372082Semi-Trendy
Sebastian130244Trendy
Seth35423Trendy
Shane52869Trendy
Shannon231132Semi-Trendy
Sharon647989Semi-Trendy
Shaun6107Trendy
Shawn215326Semi-Trendy
Sheila154361Semi-Trendy
Shelby68474Trendy
Sherri10819Trendy
Sherry173913Semi-Trendy
Shirley615887Semi-Trendy
Sierra38980Trendy
Skylar10408Trendy
Sofia117208Trendy
Sophia318523Semi-Trendy
Stacey67483Trendy
Stacy86835Trendy
Stanley95152Trendy
Stella10217Trendy
Stephanie651976Semi-Trendy
Stephen753958Semi-classic
Steve114750Trendy
Steven1216819Semi-classic
Sue10450Trendy
Susan1025728Semi-Trendy
Suzanne109387Trendy
Sydney117279Trendy
Tammy296905Trendy
Tanya22407Trendy
Tara107987Trendy
Taylor323699Semi-Trendy
Teresa298059Semi-Trendy
Terri80961Trendy
Terry346213Semi-Trendy
Thelma74017Trendy
Theodore29464Trendy
Theresa225262Semi-Trendy
Thomas2166802Classic
Tiffany283969Semi-Trendy
Tim36165Trendy
Timothy1001771Semi-classic
Tina227252Semi-Trendy
Todd207137Trendy
Tom5061Trendy
Tony96417Trendy
Tonya58234Trendy
Tracey16979Trendy
Tracy199320Trendy
Travis218731Semi-Trendy
Trevor76138Trendy
Trinity16217Trendy
Tristan27212Trendy
Troy82294Trendy
Tyler548624Semi-Trendy
Valerie70039Trendy
Vanessa119596Trendy
Vicki94504Trendy
Vickie49252Trendy
Victoria347794Semi-Trendy
Vincent23419Trendy
Violet10471Trendy
Virginia441418Semi-Trendy
Walter378194Semi-Trendy
Wanda125458Trendy
Warren13290Trendy
Wayne211347Semi-Trendy
Wendy159446Trendy
Whitney43759Trendy
William3614424Classic
Willie274564Semi-Trendy
Wyatt128168Trendy
Xavier51892Trendy
Zachary483955Semi-Trendy
Zoe78773Trendy
Zoey70140Trendy

3. Top-ranked female names since 1920

Did you find your favorite American celebrity’s name on the popularity chart? Was it classic or trendy? How do you think the name Henry did? What about Jaxon?

Since we didn’t get many traditionally female names in our classic American names search in the first task, let’s limit our search to names which were given to female babies.

We can use this opportunity to practice window functions by assigning a rank to female names based on the number of babies that have ever been given that name. What are the top-ranked female names since 1920?

In [16]:

%%sql

-- RANK names by the sum of babies who have ever had that name (descending), aliasing as name_rank
-- Select name_rank, first_name, and the sum of babies who have ever had that name
-- Filter the data for results where sex equals 'F'
-- Limit to ten results

SELECT 
RANK () OVER ( ORDER BY sum(num) DESC) name_rank, first_name, SUM(num)
FROM baby_names
WHERE sex = 'F'
GROUP BY first_name
LIMIT 10

 * postgresql:///names
10 rows affected.

Out[16]:

name_rankfirst_namesum
1Mary3215850
2Patricia1479802
3Elizabeth1436286
4Jennifer1404743
5Linda1361021
6Barbara1343901
7Susan1025728
8Jessica994210
9Lisa920119
10Betty893396

4. Picking a baby name

Perhaps a friend has heard of our work analyzing baby names and would like help choosing a name for her baby, a girl. She doesn’t like any of the top-ranked names we found in the previous task.

She’s set on a traditionally female name ending in the letter ‘a’ since she’s heard that vowels in baby names are trendy. She’s also looking for a name that has been popular in the years since 2015.

Let’s see what we can do to find some options for this friend!

In [18]:

%%sql
-- Select only the first_name column
-- Filter for results where sex is 'F', year is greater than 2015, and first_name ends in 'a'
-- Group by first_name and order by the total number of babies given that first_name

SELECT first_name FROM baby_names
WHERE (sex = 'F') and (year > 2015)and (first_name LIKE '%a')
GROUP BY first_name
ORDER BY sum(num) DESC

 * postgresql:///names
19 rows affected.

Out[18]:

first_name
Olivia
Emma
Ava
Sophia
Isabella
Mia
Amelia
Ella
Sofia
Camila
Aria
Victoria
Layla
Nora
Mila
Luna
Stella
Gianna
Aurora

5. The Olivia expansion

Based on the results in the previous task, we can see that Olivia is the most popular female name ending in ‘A’ since 2015. When did the name Olivia become so popular?

Let’s explore the rise of the name Olivia with the help of a window function.

In [20]:

%%sql

-- Select year, first_name, num of Olivias in that year, and cumulative_olivias
-- Sum the cumulative babies who have been named Olivia up to that year; alias as cumulative_olivias
-- Filter so that only data for the name Olivia is returned.
-- Order by year from the earliest year to most recent

SELECT year, first_name, num,
SUM(num) 
    OVER (ORDER BY year) 
    AS cumulative_olivias
FROM baby_names
WHERE first_name = 'Olivia'

 * postgresql:///names
30 rows affected.

Out[20]:

yearfirst_namenumcumulative_olivias
1991Olivia56015601
1992Olivia580911410
1993Olivia634017750
1994Olivia643424184
1995Olivia762431808
1996Olivia812439932
1997Olivia947749409
1998Olivia1061060019
1999Olivia1125571274
2000Olivia1285284126
2001Olivia1397798103
2002Olivia14630112733
2003Olivia16152128885
2004Olivia16106144991
2005Olivia15694160685
2006Olivia15501176186
2007Olivia16584192770
2008Olivia17084209854
2009Olivia17438227292
2010Olivia17029244321
2011Olivia17327261648
2012Olivia17320278968
2013Olivia18439297407
2014Olivia19823317230
2015Olivia19710336940
2016Olivia19380356320
2017Olivia18744375064
2018Olivia18011393075
2019Olivia18508411583
2020Olivia17535429118

6. Many males with the same name

Wow, Olivia has had a meteoric rise! Let’s take a look at traditionally male names now. We saw in the first task that there are nine traditionally male names given to at least 5,000 babies every single year in our 101-year dataset! Those names are classics, but showing up in the dataset every year doesn’t necessarily mean that the timeless names were the most popular. Let’s explore popular male names a little further.

In the next two tasks, we will build up to listing every year along with the most popular male name in that year. This presents a common problem: how do we find the greatest X in a group? Or, in the context of this problem, how do we find the male name given to the highest number of babies in a year?

In SQL, one approach is to use a subquery. We can first write a query that selects the year and the maximum num of babies given any single male name in that year. For example, in 1989, the male name given to the highest number of babies was given to 65,339 babies. We’ll write this query in this task. In the next task, we can use the code from this task as a subquery to look up the first_name that was given to 65,339 babies in 1989… as well as the top male first name for all other years!

In [22]:

%%sql

-- Select year and maximum number of babies given any one male name in that year, aliased as max_num
-- Filter the data to include only results where sex equals 'M'

SELECT year, MAX(num) AS max_num FROM baby_names
WHERE sex='M'
GROUP BY year

 * postgresql:///names
101 rows affected.

Out[22]:

yearmax_num
197085291
200034483
194794764
196285041
197568451
198068704
193160518
198168776
201318266
197271401
195690665
200724292
194888589
198467745
195792718
196186917
200230579
192560897
199254397
200822603
195890564
197177599
198564924
192661130
198864150
192959804
196383778
192860703
200329643
193062149
195187261
194062476
198268244
192056914
199935367
195287063
202019659
194687439
196881995
199638365
200525837
192357469
200921184
192460801
195488576
200427886
193862269
194277174
196679990
199836616
197467580
194986865
199065302
199541399
197367842
192761671
194166743
197767609
200132554
199737549
201419319
196581021
193556522
194476954
199444472
201619154
196085933
198763654
197867157
201819924
200624850
192158215
199349554
196482642
194380274
193761842
198664224
195386247
195985224
197666947
198965399
201219088
201120378
201920555
195588372
193959653
197967742
199160793
201718824
196985201
201519650
201022139
193259265
196782440
192257280
193354223
193455834
193658499
194574460
195086229
198368010

7. Top male names over the years

In the previous task, we found the maximum number of babies given any one male name in each year. Incredibly, the most popular name each year varied from being given to less than 20,000 babies to being given to more than 90,000!

In this task, we find out what that top male name is for each year in our dataset.

In [24]:

%%sql

-- Select year, first_name given to the largest number of male babies, and num of babies given that name
-- Join baby_names to the code in the last task as a subquery
-- Order results by year descending

SELECT year, first_name, num FROM baby_names
LEFT JOIN (SELECT year, MAX(num) AS max_num FROM baby_names
WHERE sex='M'
GROUP BY year) AS sub
USING(year)
WHERE num=max_num
ORDER BY year DESC

 * postgresql:///names
101 rows affected.

Out[24]:

yearfirst_namenum
2020Liam19659
2019Liam20555
2018Liam19924
2017Liam18824
2016Noah19154
2015Noah19650
2014Noah19319
2013Noah18266
2012Jacob19088
2011Jacob20378
2010Jacob22139
2009Jacob21184
2008Jacob22603
2007Jacob24292
2006Jacob24850
2005Jacob25837
2004Jacob27886
2003Jacob29643
2002Jacob30579
2001Jacob32554
2000Jacob34483
1999Jacob35367
1998Michael36616
1997Michael37549
1996Michael38365
1995Michael41399
1994Michael44472
1993Michael49554
1992Michael54397
1991Michael60793
1990Michael65302
1989Michael65399
1988Michael64150
1987Michael63654
1986Michael64224
1985Michael64924
1984Michael67745
1983Michael68010
1982Michael68244
1981Michael68776
1980Michael68704
1979Michael67742
1978Michael67157
1977Michael67609
1976Michael66947
1975Michael68451
1974Michael67580
1973Michael67842
1972Michael71401
1971Michael77599
1970Michael85291
1969Michael85201
1968Michael81995
1967Michael82440
1966Michael79990
1965Michael81021
1964Michael82642
1963Michael83778
1962Michael85041
1961Michael86917
1960David85933
1959Michael85224
1958Michael90564
1957Michael92718
1956Michael90665
1955Michael88372
1954Michael88576
1953Robert86247
1952James87063
1951James87261
1950James86229
1949James86865
1948James88589
1947James94764
1946James87439
1945James74460
1944James76954
1943James80274
1942James77174
1941James66743
1940James62476
1939Robert59653
1938Robert62269
1937Robert61842
1936Robert58499
1935Robert56522
1934Robert55834
1933Robert54223
1932Robert59265
1931Robert60518
1930Robert62149
1929Robert59804
1928Robert60703
1927Robert61671
1926Robert61130
1925Robert60897
1924Robert60801
1923John57469
1922John57280
1921John58215
1920John56914

8. The most years at number one

Noah and Liam have ruled the roost in the last few years, but if we scroll down in the results, it looks like Michael and Jacob have also spent a good number of years as the top name! Which name has been number one for the largest number of years? Let’s use a common table expression to find out.

In [26]:

%%sql

-- Select first_name and a count of years it was the top name in the last task; alias as count_top_name
-- Use the code from the previous task as a common table expression
-- Group by first_name and order by count_top_name descending

SELECT first_name, COUNT(year) AS count_top_name FROM
(SELECT year, first_name, num FROM baby_names
LEFT JOIN (SELECT year, MAX(num) AS max_num FROM baby_names
WHERE sex='M'
GROUP BY year) AS sub
USING(year)
WHERE num=max_num
ORDER BY year DESC ) AS ctable 

GROUP BY first_name
ORDER BY count_top_name DESC

 * postgresql:///names
8 rows affected.

Out[26]:

first_namecount_top_name
Michael44
Robert17
Jacob14
James13
Noah4
John4
Liam4
David1

Leave a comment

Hello !!! I’m Ram Rallabandi

an AI consultant and data scientist with a proven track record of delivering high-impact AI solutions. My mission is simple: transform complex AI strategies into measurable business outcomes.

→ What I Do:
I specialize in leading the end-to-end lifecycle of AI products, from ideation to technical development to successful market launch. My expertise lies in bridging the gap between technical innovation and business value to ensure that every AI initiative aligns with organizational goals.

→ How I Add Value:
☑ Drive the adoption of responsible AI practices to future-proof businesses
☑ Build scalable, high-quality AI solutions that directly address customer needs
☑ Streamline processes to connect AI demand with delivery teams efficiently
☑ Analyze market trends to position data-driven solutions for maximum impact

Why It Works:
With a strong foundation in AI, machine learning, and data science, I provide strategic insights that empower organizations to leverage cutting-edge technologies for growth. I collaborate with technical and business teams to:
↳ Define product roadmaps
↳ Manage budgets, timelines, and scopes
↳ Foster cross-functional alignment for AI-driven innovation

My Passion:
I thrive on leading AI storytelling for executive leadership and fostering collaboration across diverse teams. With deep experience in Fintech and Payments, I’m dedicated to unlocking AI’s potential to deliver business transformation and create widely adopted solutions.

Let’s connect to explore how AI can transform your business.

Join the fun!

Stay updated with our latest tutorials and ideas by joining my newsletter.

Recent posts