top

DefinitionCopy Anchor Link

Find the top N values of a dimension, based on specified measures. The top rows are determined by the specified measures, in descending order. Default to use 'skip' logic, which is the same as the rank function. To use 'dense' logic, use the logic: 'dense' parameter.

Syntax

top(n, dimension, by: measure_expr, ...)
top(n, dimension, by: measure_expr, ..., logic: logic)

top(10, users.name, by: count(orders.id)) // -> Table(users.name)
top(10, users.name, by: count(orders.id), by: average(users.age)) // -> Table(users.name)
top(10, users.name, by: count(orders.id), logic: 'dense') // -> Table(users.name)

Input

• n (required): The number of top values to return.
• dimension (required): A fully-qualified reference to a dimension. The output table will have one row for each top value of the specified dimension.
• by (repeatable, required): A measure that is used for ranking. The top rows are determined by the specified measure, in descending order. E.g. top(10, users.name, by: count(orders.id))
• logic (optional): The logic to use for ranking. Default to 'skip'. To use 'dense' logic, use logic: 'dense'. E.g. top(10, users.name, by: count(orders.id), logic: 'dense')

Output

A new table with one row for each top value of the specified dimension.

Sample UsagesCopy Anchor Link

top(5, users.full_name, by: count(orders.id))

Notice that the their are 6 users in the top 5 because there are users with the same number of orders.

Using dense rank logic would return even more users because their rank is consecutive. E.g. The users below the 2 top-1 users would be ranked 2 instead of skipping to 3.

top(5, users.full_name, by: count(orders.id), logic: 'dense')

Top is especially useful when used with where to filter measure to only include the top values.

explore {
  measures {
    _total_orders_of_top_5:   
      count(orders.id) | where(users.id in top(5, users.id, by: count(orders.id)))
  }
}

You can also use it for nested aggregation, such as finding the top 5 users by number of orders, then finding the average age of those users. (Note: orders.value is a measure)

top(5, users.id, by: count(orders.id)) | select(orders.value) | avg()

See alsoCopy Anchor Link

• bottom()
• group()
• rank()
• Pipe operator