MIP Logo

Analysing names with SQL pt. 1

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

Background

This week at The Data School, we learnt some basic and intermediate SQL. SQL was my first love in the data industry. After I decided to pivot my teaching career into data analytics, I spent the first few months learning SQL on DataCamp and various sites offering interview-style SQL challenges. As I’m sitting in the middle of the airport waiting for my flight to Perth, I had a look into my old DBMS and found this old dataset that I’d been meaning to play around with.

Dataset

This project is a data exploration of name trends in the USA using dataset obtained from the United States Social Security Administration. For the purpose of this task, I defined popularity as being chosen more than 5000 times – a condition I set when I created the usa_baby_names view. Another thing worth noting is only first names are considered in this project, as last names are usually inherited anyway.

Questions

1. Favourite names across the century

I included count of 100 years in HAVING statement, which ensures the results contains names that has appeared for 100 years.

Query:

Result:

2. Timeless or just trendy?

Using CASE WHEN statements, I assign labels to names based on the number of times they appeared in the dataset: more than 80 times is considered ‘Classic’, more than 50: ‘Semi-classic’, more than 20: ‘Semi-trendy’, less than 20: ‘Trendy’.

Query:

Result:
3. Top 10 girl names

Using RANK statement, I assign name popularity ranking based on the number of times they appear on the dataset.

4. Choosing a name for an expecting friend

A friend of mine is expecting a girl and has started considering names. She’d once mentioned that she likes names ending with the -ah sound. I thought the closest way to achieve that is to look for names ending with an A.

Query:

Result:
5. Running total of Olivia’s

Now that I know Olivia has been the most popular girl name ending with an A – unexpected result – I wanted to see when it got popular. To do this, we need the running totals. Again, here we utilise window functions. Using pgAdmin’s built in visualiser, I plotted a simple line chart.

Query:

Result:

Conclusion

Since early 90’s, the name Olivia steadily rises to popularity, and now is the most popular baby names in the USA through the century.
Part 2 is coming up with boy names.

Share this post