Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Top N

KATER__IS_IN_BOTTOM

True where the value ranks in the bottom count of the query's rows, ties included.

Family: Top N

Syntax 

KATER__IS_IN_BOTTOM(field, count) [OVER (PARTITION BY ...)]

Arguments

ArgumentDescription
fieldThe value to rank by, usually a measure’s value, such as EVAL(orders.sum__amount).
countHow many ranks to keep, from the lowest value up. A whole number.

Returns

TRUE or FALSE.

Usage notes

  • Write it in QUALIFY. It ranks the rows of the query’s result.
  • It compiles to (RANK() OVER (ORDER BY field ASC NULLS LAST) <= count AND NOT field IS NULL). Rows that tie all stay, so more than count rows can come back.
  • Write OVER (PARTITION BY ...) to rank within each value of those fields, such as each currency. It orders its own rows, so its OVER takes no ORDER BY.
  • An empty value is never in the bottom count.
  • It cannot rank by a window value, such as a rank or a running total.
  • You write count in the call. A parameter reaches only an OPTIONAL_FILTER in WHERE, so the compiler refuses one in QUALIFY with parameterized_predicate_not_optional.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

SELECT
  orders.currency AS "Currency",
  orders.channel AS "Channel",
  EVAL(orders.count_distinct__order_id) AS "Orders"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.currency,
  orders.channel
QUALIFY
  KATER__IS_IN_BOTTOM(orders.count_distinct__order_id, 1) OVER (PARTITION BY orders.currency)
ORDER BY
  1
CurrencyChannelOrders
EURweb127
USDweb579

Compiles to

The call compiles to the same SQL on DuckDB and on Snowflake.

(
  RANK() OVER (PARTITION BY orders.currency ORDER BY orders.count_distinct__order_id ASC) <= 1
  AND NOT orders.count_distinct__order_id IS NULL
)

See also