1 hidden cell
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;WITH name_years AS (
SELECT first_name, COUNT(DISTINCT year) AS year_count, SUM(num) AS sum
FROM baby_names
GROUP BY first_name
),
classified_names AS (
SELECT first_name, sum,
CASE WHEN year_count >= 50 THEN 'Classic'
ELSE 'Trendy'
END AS popularity_type
FROM name_years
)
SELECT *
FROM classified_names
ORDER BY first_name
LIMIT 5;WITH male_name AS(
SELECT first_name, SUM(num) AS sum
FROM baby_names
WHERE sex ='M'
GROUP BY first_name
),
ranked_names AS (
SELECT first_name,
sum,
RANK() OVER(ORDER BY sum DESC) AS name_rank
FROM male_name
)
SELECT *
FROM ranked_names
WHERE name_rank <= 20
OR first_name = 'Paul';WITH name_1920 AS(
SELECT DISTINCT first_name
FROM baby_names
WHERE sex= 'F' AND year =1920
),
name_2020 AS (
SELECT DISTINCT first_name
FROM baby_names
WHERE sex = 'F' AND year = 2020
),
common_name AS (
SELECT n1920.first_name
FROM name_1920 n1920
INNER JOIN name_2020 n2020
ON n1920.first_name = n2020.first_name
),
total_counts AS (
SELECT first_name, SUM(num) AS total_occurrences
FROM baby_names
WHERE sex= 'F'
GROUP BY first_name
)
SELECT tc.first_name, tc.total_occurrences
FROM total_counts tc
JOIN common_name cn
ON tc.first_name = cn.first_name;