Skip to main content

rank

Knowledge Checkpoint

Readings that will help you understand this documentation better:

Definition

A window function that returns the rank of rows within a partition of a table. Tie values are assigned the same rank. The next rank in the sequence is not consecutive. E.g. 1, 1, 3, 4, 4, 6, ...

To get consecutive rank, use dense_rank.

Syntax

rank(order: order_expr)
rank(order: order_expr, ..., partition: partition_expr, ...)
Examples
rank(order: count(orders.id) | desc())
rank(order: count(orders.id), order: average(users.age))

// with partition
rank(order: count(orders.id), partition: orders.status)

Input

  • order (required, repeatable): A field that is used for ordering. The order is default to ascending. The order can be set explicitly with asc() or desc(). E.g. rank(order: count(orders.id) | desc())
  • partition (repeatable, optional): A field that is used for partitioning the table. E.g. rank(order: count(orders.id), partition: orders.status)

Output

Rank of the current row within its partition (if no partition is specified, the whole table is considered as a single partition). If two rows are tied for a rank, each tied rows receives the same rank. The next rank in the sequence is not consecutive. E.g. 1, 1, 3, 4, 4, 6, ...

Sample Usages

Rank top buyer by gender and age group
explore {
dimensions {
users.gender, users.age_group
}
measures {
count(orders.id),
_rank: rank(order: count(orders.id) | desc())
}
}
Rank top buyer by age group partition by gender
explore {
dimensions {
users.gender, users.age_group
}
measures {
count(orders.id),
_rank: rank(order: count(orders.id) | desc(), partition: users.gender)
}
}

Using rank in a dimension definition is straight-forward. Remember that the rank is calculated against all rows in the model.

dimension ranking_by_age {
label: 'Rank By Age'
type: 'number'
hidden: false
description: ''
definition: @aql rank(order: users.age | desc()) ;;
}

You can see that users with the same age are assigned the same rank

Rank users by Age
explore {
dimensions {
users.age,
users.ranking_by_age
}
}

You can also use it in filter() to filter out the top 10 users by age:

Filter top 1 Youngest Users
users 
| select(users.full_name, _rank: rank(order: users.age))
| filter(_rank == 1)

Let us know what you think about this document :)