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.
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:
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:
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:
Conclusion
