top

Definition

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)

Examples

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 Usages

Top 5 users by Number of Orders

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

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."full_name" AS "users->full_name"
  FROM
    "demo"."users" "users"
  GROUP BY
    1
), "aql__t2" AS (
  SELECT
    "users"."full_name" AS "users->full_name",
    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
), "aql__t3" AS (
  SELECT
    "aql__t1"."users->full_name" AS "users->full_name",
    RANK() OVER ( ORDER BY "aql__t2"."count_orders->id" DESC) AS "_rank"
  FROM
    "aql__t1"
    LEFT JOIN "aql__t2" ON "aql__t1"."users->full_name" = "aql__t2"."users->full_name" OR ("aql__t1"."users->full_name" IS NULL AND "aql__t2"."users->full_name" IS NULL)
  WHERE
    "aql__t2"."count_orders->id" IS NOT NULL
)
SELECT
  "aql__t3"."users->full_name" AS "users->full_name"
FROM
  "aql__t3"
WHERE
  "aql__t3"."_rank" <= 5

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 by Number of Orders using Dense Rank

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

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."full_name" AS "users->full_name"
  FROM
    "demo"."users" "users"
  GROUP BY
    1
), "aql__t2" AS (
  SELECT
    "users"."full_name" AS "users->full_name",
    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
), "aql__t3" AS (
  SELECT
    "aql__t1"."users->full_name" AS "users->full_name",
    DENSE_RANK() OVER ( ORDER BY "aql__t2"."count_orders->id" DESC) AS "_rank"
  FROM
    "aql__t1"
    LEFT JOIN "aql__t2" ON "aql__t1"."users->full_name" = "aql__t2"."users->full_name" OR ("aql__t1"."users->full_name" IS NULL AND "aql__t2"."users->full_name" IS NULL)
  WHERE
    "aql__t2"."count_orders->id" IS NOT NULL
)
SELECT
  "aql__t3"."users->full_name" AS "users->full_name"
FROM
  "aql__t3"
WHERE
  "aql__t3"."_rank" <= 5

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

Total Orders of the top 5 Users by Number of Orders

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

Open in AMQL playground

Result

SQL

WITH "aql__t3" AS (
  SELECT
    "users"."id" AS "users->id"
  FROM
    "demo"."users" "users"
  GROUP BY
    1
), "aql__t4" AS (
  SELECT
    "users"."id" AS "users->id",
    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
), "aql__t5" AS (
  SELECT
    "aql__t3"."users->id" AS "users->id",
    RANK() OVER ( ORDER BY "aql__t4"."count_orders->id" DESC) AS "_rank"
  FROM
    "aql__t3"
    LEFT JOIN "aql__t4" ON "aql__t3"."users->id" = "aql__t4"."users->id" OR ("aql__t3"."users->id" IS NULL AND "aql__t4"."users->id" IS NULL)
  WHERE
    "aql__t4"."count_orders->id" IS NOT NULL
), "aql__t6" AS (
  SELECT
    "aql__t5"."users->id" AS "users->id"
  FROM
    "aql__t5"
  WHERE
    "aql__t5"."_rank" <= 5
)
SELECT
  COUNT("orders"."id") AS "_total_orders_of_top_5"
FROM
  "demo"."orders" "orders"
  LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  LEFT JOIN "aql__t6" ON "users"."id" = "aql__t6"."users->id"
WHERE
  "aql__t6"."users->id" IS NOT NULL

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)

Average Value of the top 5 Users by Number of Orders

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

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "users"."id" AS "users->id"
  FROM
    "demo"."users" "users"
  GROUP BY
    1
), "aql__t2" AS (
  SELECT
    "users"."id" AS "users->id",
    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
), "aql__t3" AS (
  SELECT
    "aql__t1"."users->id" AS "users->id",
    RANK() OVER ( ORDER BY "aql__t2"."count_orders->id" DESC) AS "_rank"
  FROM
    "aql__t1"
    LEFT JOIN "aql__t2" ON "aql__t1"."users->id" = "aql__t2"."users->id" OR ("aql__t1"."users->id" IS NULL AND "aql__t2"."users->id" IS NULL)
  WHERE
    "aql__t2"."count_orders->id" IS NOT NULL
), "aql__t4" AS (
  SELECT
    "aql__t3"."users->id" AS "users->id"
  FROM
    "aql__t3"
  WHERE
    "aql__t3"."_rank" <= 5
), "aql__t5" AS (
  SELECT
    "users"."id" AS "users->id",
    SUM(("order_items"."quantity" * "products"."price")) AS "sum_c7839"
  FROM
    "demo"."order_items" "order_items"
    LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"
    LEFT JOIN "demo"."orders" "orders" ON "order_items"."order_id" = "orders"."id"
    LEFT JOIN "demo"."users" "users" ON "orders"."user_id" = "users"."id"
  GROUP BY
    1
)
SELECT
  AVG("aql__t5"."sum_c7839") AS "avg_orders->value"
FROM
  "aql__t4"
  LEFT JOIN "aql__t5" ON "aql__t4"."users->id" = "aql__t5"."users->id" OR ("aql__t4"."users->id" IS NULL AND "aql__t5"."users->id" IS NULL)