dense_rank

DefinitionCopy Anchor Link

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 consecutive. E.g. 1, 1, 2, 3, 3, 4, ...

To return non-consecutive rank, use rank.

Syntax

dense_rank(order: order_expr)
dense_rank(order: order_expr, ..., partition: partition_expr, ...)

dense_rank(order: count(orders.id) | desc())
dense_rank(order: count(orders.id), order: average(users.age))

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

// Axis-aware examples
dense_rank(order: 'rows')                              // Dense rank by row order
dense_rank(order: 'columns' | desc())                  // Dense rank by column order (descending)
dense_rank(order: revenue | desc(), partition: 'rows') // Dense rank within each row

Input

• order (required, repeatable): A field that is used for ordering. The order defaults to ascending. The order can be set explicitly with asc() or desc(). You can also use axis references:
  • 'rows' or 'x_axis': Order by dimensions mapped to rows/x-axis
  • 'columns' or 'legend': Order by dimensions mapped to columns/legend
  • E.g. dense_rank(order: count(orders.id) | desc()) or dense_rank(order: 'rows')
• partition (repeatable, optional): A field that is used for partitioning the table. You can also use axis references like 'rows', 'columns', 'x_axis', or 'legend'. E.g. dense_rank(order: count(orders.id), partition: orders.status) or dense_rank(order: revenue, partition: 'rows')

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 consecutive. E.g. 1, 1, 2, 3, 3, 4, ...

Sample UsagesCopy Anchor Link

explore {
  dimensions {
    users.gender, users.age_group
  }
  measures {
    count(orders.id),
    _rank: dense_rank(order: count(orders.id) | desc())
  }
}

explore {
  dimensions {
    users.gender, users.age_group
  }
  measures {
    count(orders.id),
    _rank: dense_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 dense_rank(order: users.age | desc()) ;;
}

explore {
  dimensions {
    users.age,
    users.ranking_by_age
  }
}

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

users 
| select(users.full_name, _rank: dense_rank(order: users.age))
| filter(_rank == 1)

See alsoCopy Anchor Link

• rank(): same value gets same rank, but next rank skips ahead
• percent_rank()
• ntile()
• Window Functions