Filter a result by its relative rank with neighbors

I have a list of product SKUs and the revenue they generated in a certain period.

The list of SKUs is really long, so I’d like to able to filter to a specific SKU within that list and return the surrounding 10 products (5 above and 5 below) when ranked by revenue.

The user would filter the SKU to 30010492910, and the Look would return 11 rows, with the filtered SKU in the middle, ranked by revenue. Displaying the rank would be ideal.

Here’s an example of the expected output (with fewer rows):

Rank SKU Revenue
45 30012030122 80,000
46 30012032351 79,000
47 30010492910 72,000
48 30012030199 68,500
49 30012033921 68,000

Any idea how to accomplish this?

Sorry for the late reply Josh! If you haven’t figured this out already,I think you could do this with some subqueries in SQL. Not sure if there’s a Looker magic spell here that will make things that much simpler, but filters sure are nice.

It’s not too complex as a derived table. I used a movies dataset we’ve got:

view: rank_dt {
  derived_table: {
    sql: 
    WITH ranks AS (SELECT 
	movies.title  AS title,
	SUM(movies.revenue) AS revenue,
	RANK() OVER (ORDER BY SUM(movies.revenue) DESC) AS rank
FROM mak_movies.movies  AS movies
GROUP BY 1
ORDER BY 2 DESC)

SELECT * FROM ranks
WHERE {% if title_filter._is_filtered %} 
rank <= (SELECT rank FROM ranks WHERE title = {% parameter title_filter %}) + 5 AND rank >= (SELECT rank FROM ranks WHERE title = {% parameter title_filter %}) -5
      {% else %}
      1=1
      {% endif %};;
  }
  
  filter: title_filter {
    type: string
  }
  
  dimension: movie {
    type: string
    sql: ${TABLE}.title ;;
  }

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

 }
1 Like