dense_rank

Knowledge Checkpoint

Readings that will help you understand this documentation better:

• Window Functions Overview

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 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, ...)

Examples

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 Usages

Rank top buyer by gender and age group

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

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."gender" AS "users->gender",
    (case
    when extract(year from age("users"."birth_date")) > 60 then 'Old'
    when extract(year from age("users"."birth_date")) > 30 then 'Mature'
    when extract(year from age("users"."birth_date")) > 19 then 'Young Adult'
    when extract(year from age("users"."birth_date")) > 13 then 'Teen'
    else 'Child'
    end) AS "users->age_group",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1,
    2
)
SELECT
  "aql__t1"."users->gender" AS "users->gender",
  "aql__t1"."users->age_group" AS "users->age_group",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  DENSE_RANK() OVER ( ORDER BY "aql__t1"."count_orders->id" DESC) AS "_rank"
FROM
  "aql__t1"

Rank top buyer by age group partition by gender

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

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."gender" AS "users->gender",
    (case
    when extract(year from age("users"."birth_date")) > 60 then 'Old'
    when extract(year from age("users"."birth_date")) > 30 then 'Mature'
    when extract(year from age("users"."birth_date")) > 19 then 'Young Adult'
    when extract(year from age("users"."birth_date")) > 13 then 'Teen'
    else 'Child'
    end) AS "users->age_group",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1,
    2
)
SELECT
  "aql__t1"."users->gender" AS "users->gender",
  "aql__t1"."users->age_group" AS "users->age_group",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  DENSE_RANK() OVER (PARTITION BY "aql__t1"."users->gender" ORDER BY "aql__t1"."count_orders->id" DESC) AS "_rank"
FROM
  "aql__t1"

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()) ;;
}

Rank users by Age

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

Result

SQL

WITH "aql__t1" AS (
  SELECT
    extract(year from age("users"."birth_date")) AS "age",
    "users"."birth_date" AS "birth_date",
    DENSE_RANK() OVER ( ORDER BY extract(year from age("users"."birth_date")) DESC) AS "ranking_by_age"
  FROM
    "demo"."users" "users"
)
SELECT
  "users"."age" AS "users->age",
  "users"."ranking_by_age" AS "users->ranking_by_age"
FROM
  "aql__t1" "users"
GROUP BY
  1,
  2

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: dense_rank(order: users.age))
| filter(_rank == 1)

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."full_name" AS "users->full_name",
    DENSE_RANK() OVER ( ORDER BY extract(year from age("users"."birth_date")) ASC) AS "_rank"
  FROM
    "demo"."users" "users"
)
SELECT
  "aql__t1"."users->full_name" AS "users->full_name",
  "aql__t1"."_rank" AS "_rank"
FROM
  "aql__t1"
WHERE
  "aql__t1"."_rank" = 1

See also

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