SQL Bigquery tidbits: Popular names 1910-2013 database
The table:
Let's start with most popular name throughout all the years:
SELECT A.*, B.*
FROM `bigquery-public-data.usa_names.usa_1910_2013` A
inner join
(select name as name2, sum(number) as res from `bigquery-public-data.usa_names.usa_1910_2013`
group by name order by sum(number) desc) B
on A.name = B.name2
order by res desc
alternatively:
with cte as (
SELECT A.*,
sum(number) over (partition by name) as res
FROM `bigquery-public-data.usa_names.usa_1910_2013` A
)
select * from cte order by res desc
Result: both time JAMES. Obviously :D
Most popular female name then?
with cte as (
SELECT A.*,
sum(number) over (partition by name, gender) as res
FROM `bigquery-public-data.usa_names.usa_1910_2013` A
)
select * from cte
where gender = 'F'
order by res desc
MARY.
How many different names? That's a lotta names.
Which year had the most name occurences (aka births)? (according to the database at least)
1957.
