Name Game: step 5 - using rank to figure out name density and popularity

  • 28 March 2022
  • 0 replies

Userlevel 5

This content, written by Lloyd Tabb, was initially posted in Looker Blog on Jan 15, 2016. The content is subject to limited support.

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 , 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: |
        RANK() OVER (
          PARTITION BY year, state, gender 
          ORDER BY number DESC) as name_rank
      FROM names) 

  - dimension: name_rank
    type: number

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


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

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.

It looks like names are getting more diverse over time.

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

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.

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

Here's a list of all of the steps:

0 replies

Be the first to reply!