bottom

Definition

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

Syntax

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

Examples

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

Input

• n (required): The number of bottom values to return.
• dimension (required): A fully-qualified reference to a dimension. The output table will have one row for each bottom value of the specified dimension.
• by (repeatable, required): A measure that is used for ranking. The bottom rows are determined by the specified measure, in ascending order. E.g. bottom(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. bottom(10, users.name, by: count(orders.id), logic: 'dense')

Output

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

Sample Usages

Bottom 5 users by Number of Orders

bottom(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" ASC) 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 bottom 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 bottom-1 users would be ranked 2 instead of skipping to 3.

Bottom 5 users by Number of Orders using Dense Rank

bottom(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" ASC) 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

Bottom is especially useful when used with where to filter measure to only include the bottom values.

Total Orders of the bottom 5 Users by Number of Orders

explore {
  measures {
    _total_orders_of_top_5:   
      count(orders.id) | where(users.id in bottom(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" ASC) AS "_rank"
  FROM
    "aql__t3"
    LEFT JOIN "aql__t4" ON "aql__t3"."users->id" = "aql__t4"."users->id"
  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
  GROUP BY
    1
)
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"
  INNER JOIN "aql__t6" "__aql_sm_1__" ON "users"."id" = "__aql_sm_1__"."users->id"

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

Average Value of the bottom 5 Users by Number of Orders

bottom(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" ASC) AS "_rank"
  FROM
    "aql__t1"
    LEFT JOIN "aql__t2" ON "aql__t1"."users->id" = "aql__t2"."users->id"
  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"