1. Classic American names
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
baby_names| column | type | meaning |
|---|---|---|
year | int | year |
first_name | varchar | first name |
sex | varchar | sex of babies given first_name |
num | int | number 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!
%%sql
postgresql:///names
-- To find baby names that appear in all years, we group every first names, and count the number of
-- years for each name. If they exist in all 101 years, that means there were at least 5000 babies
-- who were named that name. We use HAVING to filter the aggregation condition.
SELECT first_name, SUM(num)
FROM baby_names
GROUP BY first_name
HAVING COUNT(year) = 101
ORDER BY SUM(num) DESC;
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.
%%sql
-- We want to make a classification row that bases the classification based on how many years
-- The name showed up in the database (How many years 5000 babies or more were named that name)
-- We use a CASE statement in the SELECT keyword to do this.
-- We also order by alphabetical order, for easier searching.
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 ASC;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?
%%sql
-- We want to find the most popular female names, by quantity.
-- To do this, we use the RANK() function inside the WHERE
-- This gives us a column based on the sum column which counts the total number of babies named first_name
SELECT first_name, SUM(num),
RANK() OVER(ORDER BY SUM(num) DESC) AS name_rank
FROM baby_names
WHERE sex = 'F'
GROUP BY first_name
-- Only want the top 10 female names
LIMIT 10;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!
%%sql
-- Select only the first_name column
SELECT first_name
FROM baby_names
-- Filter for results where sex is 'F', year is greater than 2015, and first_name ends in 'a'
WHERE sex = 'F' AND year > 2015 AND first_name LIKE '%a'
-- Group by first_name and order by the total number of babies given that first_name
GROUP BY first_name
ORDER BY sum(num) DESC;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.
%%sql
-- To see when the name 'Olivia' got popular, we can filter for only the name Olivia
-- We then can get a rolling sum of all Olivias to see when the popularity of the name skyrocketed.
SELECT year, first_name, num,
SUM(num) OVER (ORDER BY year ASC) AS cumulative_olivias
FROM baby_names
WHERE first_name = 'Olivia'
ORDER BY year ASC;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!
%%sql
-- Select year and maximum number of babies given any one male name in that year, aliased as max_num
SELECT year, MAX(num) AS max_num
FROM baby_names
WHERE sex = 'M'
GROUP BY year7. 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.
%%sql
-- Select year, first_name given to the largest number of male babies, and num of babies given that name
SELECT b.year, b.first_name, b.num
FROM baby_names as b
INNER JOIN (
SELECT year, MAX(num) AS max_num
FROM baby_names
WHERE sex = 'M'
GROUP BY year) AS subquery
ON subquery.year = b.year AND subquery.max_num = b.num
ORDER BY year DESC;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.