This article is about dynamically filtering dimensions based on an aggregation. You can use the examples from this article to filter a dimension by its relative ranking over any metric. The most common use case is filtering pivoted data by revenue (or other aggregation), rather than an alphanumeric string, in order to see only the top N pivoted values.

This analytical pattern results in a visualization such as the following, based on an e-commerce dataset:

Dynamic Rankings in Looker

For example, if we want to look at the number of users in each country, but want to filter the countries by the top 10 in revenue, we might think to build a is_top_10 dimension like this in our Orders view:

dimension: is_top_10 {
  type: yesno
  sql:
    exists(
      select *
      from (
        select country
        from orders
        group by country
        order by sum(revenue) desc
        limit 10
      ) top_10
      where ${country} = top_10.country
    ) ;;
}

This approach will work; however, it's not best practice for two reasons:

1. The user can only look at the top 10, instead of dynamically querying the top N.
2. The top 10 are being calculated over all time, which may not line up with filters created in the Explore section.

Here is what we can do to address these issues:

1. Instead of is_top_10, create a country_rank dimension we can filter on to get any arbitrary top N countries. In the example below, this dimension is called rank.
2. Build the query in an ephemeral derived table, and create a filter field that will appear as a front end filter where users can input a date value. We can reference the value the user inputs into the filter in the WHERE clause of the derived table to determine rankings using a different timeframe than the one being applied to the orders table. We do this by referencing the filter field in a templated filter. For example, we might want to filter down this dataset to output this year's top 10 countries, but ultimately filter the orders Explore only for the last month. We can achieve this with a dynamic derived table using templated filters.

Here is the code:

MySQL Example

view: country_revenue_ranks {
  derived_table: {
    sql:
      select
      country
      , cast(@curRank := @curRank + 1 as int) as rank
      from (
        select country
        from orders
        where {% condition rank_date_filter %} orders.created_at {% endcondition %}
        group by country
        order by sum(revenue) desc) ordered_list, (select @curRank := 0);;
  }

  filter: rank_date_filter {
    type: date
  }

  dimension: country {
    primary_key: yes
    sql: ${TABLE}.country ;;
  }

  dimension: rank {
    type: number
    sql: ${TABLE}.rank ;;
  }
}

NOTE: The solution for dialects with window functions (Redshift) would use ROW_NUMBER() instead of the @variable iteration used above.

Postgres Example

derived_table: {
  sql:
    select
    country
    , sum(revenue) as duration
    , rank() over (order by sum(revenue) desc) as rank
    from orders
    where
      {% condition rank_date_filter %} orders.date {% endcondition %}
    group by 1
    ;;
  }

  filter: rank_date_filter {
    type: date
  }

  dimension: country {
    primary_key: yes
    sql: ${TABLE}.country ;;
  }

  dimension: rank {
    type: number
    sql: ${TABLE}.rank ;;
  }
}

For an example of implementing this analytic pattern using bind_all_filters with a Native Derived Table, see this Community post.

NOTE: A derived table cannot be persisted if it makes use of a templated filter. There are potentially an infinite number of possible user inputs, so the number of persistent tables in your database could potentially become unmanageable.