Dynamic Rankings (Filtering Dimensions by Custom Rank)

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

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.

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.
Version history
Last update:
‎05-12-2022 02:08 PM
Updated by: