Looker Blog : Data Matters

Name Game: Step 5 - Using Rank to Figure out Name Density and Popularity

Lloyd Tabb, Founder & CTO

Jan 15, 2016

Step 5: Using Rank to Figure out Name Density and Popularity

Another way to figure out popularity is to use ranking. In your high school class school cohort, you probably achieved some class rank based on your grades. We're going to essentially do the same thing by ranking names within their cohort of gender, year, and state. The most popular male name in a given state year will achieve rank '1' and then on down.

We can use this to figure out some interesting trends about names.

The Code

We'll inherit the model we used in Step 4, including the join to the cohort table.

# rank names within state and year to find name density    
- explore: names_step_5
  extends: names_step_4

Next, we turn the base table into a derived table which includes all the data from the original table and also computes name_rank using a window function.

We'll then add a dimension for the name_rank and a tiered dimension (so we can look at trends in the rankings).

# Rank names within states and years to understand name
#  density.
#
- view: names_step_5
  extends: names_step_4
  sql_table_name: |
      (SELECT 
        *,
        RANK() OVER (
          PARTITION BY year, state, gender 
          ORDER BY number DESC) as name_rank
      FROM names) 

  fields:
  - dimension: name_rank
    type: number

  - dimension: name_rank_tiered
    type: tier
    tiers: [10,30,100,300,1000]
    style: integer
    sql: ${name_rank}

Explore!

All of the queries below can be created by Exploring From Here. Select dimensions and measures, add filters and bonk the 'Run Button'.

Name Density

It looks like most of the population doesn't appreciate name diversity. Twenty two percent of the population is named with the top 9 names of their cohort. Over 40 percent of the population has been named in the top 30 names of their cohort.

Explore From Here

Name Density by Decade

It looks like names are getting more diverse over time.

Explore From Here

Name Density by Gender

As we've seen in other graphs, We tend to name our girls more diversely than our boys.

Explore From Here

Name Density by State

Does where you live make it more likely that you will be given an uncommon name? Let's look at how much of the population makes up the top 100 names of their cohort by state. Looks like Texas and California have pretty diverse names, but 92% of Wyoming's population have names in the top 100.

Explore From Here

Name Density by State before 1940

The map shifts darker when looking before 1940, but California and Texas are still more diverse than everyone else.

Explore From Here

Here's a list of all of the steps:

Next Previous

Subscribe for the Latest Posts