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 you'll practice while answering these queries are broadly applicable. After all, understanding trends and popularity is important for many businesses, too!
You'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, the dataset is limited to first names which were given to over 5,000 American babies in a given year. The data spans 101 years, from 1920 through 2020.
The Data
baby_names
baby_names| column | type | description |
|---|---|---|
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 |
-- Run this code to view the data in baby_names
SELECT *
FROM baby_names
LIMIT 5;-- Use this table for the answer to question 1:
-- List the overall top five names in alphabetical order and find out if each name is "Classic" or "Trendy."
Select first_name, sum(num) sum, popularity_type
FROM
(Select *, count(year) over(partition by first_name) years_present, case when count(year) over(partition by first_name) >= 50 then 'Classic' else 'Trendy' end popularity_type
FROM public.baby_names) as p
group by first_name, popularity_type
order by first_name asc
limit 5;-- Use this table for the answer to question 2:
with ranked_male_names as
(Select rank() over(order by sum desc) name_rank, first_name, sum
FROM
(Select first_name, sum(num) sum
From baby_names
where sex = 'M'
group by first_name) as oa)
Select * from ranked_male_names
where name_rank <= 20;-- Use this table for the answer to question 3:
Select ntw.first_name, (num_1920 + num_2020) total_occurrences
from
(Select first_name, num num_1920
from public.baby_names
where year = 1920 and sex = 'F') as ntw
JOIN
(Select first_name, num num_2020
from public.baby_names
where year = 2020 and sex = 'F') as twtw
on ntw.first_name = twtw.first_name;