Skip to main content

# KATER__BUCKET

A label for the band of numbers that each value is in.

{/* Written by apps/landing-page/scripts/docs/write_kater_functions.py from the compiler's function registry, kater_sql/function_docs.py and scripts/docs/sql-language.md. Do not edit it by hand: change those, then run the script. */}

**Family:** [Derive](https://kater.ai/docs/sql-reference/functions/derive/)

## Syntax

```sql
KATER__BUCKET(field, edges [, labels => [label, ...]])
```

## Arguments

**Required**

| Argument | Description |
| --- | --- |
| `field` | The number to put in bands. |
| `edges` | The numbers where one band stops and the next starts, from the lowest to the highest. A list such as `[10, 100]`. |

**Optional**

| Argument | Description |
| --- | --- |
| `labels` | A label for each band, from the lowest band up. Give one more label than edges. A list such as `['Small', 'Large']`. |

## Returns

Text, or NULL where the field is empty.

## Usage notes

- Each band includes its lower edge. With `[25, 100]`, 25 is in the band `25 – 100`, and 100 is in `≥ 100`.
- Without labels, the bands are named `< 25`, `25 – 100` and `≥ 100`.

## Example

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

```sql {2,6}
SELECT
  KATER__BUCKET(orders.amount, [50, 100, 200]) AS "Amount Band",
  EVAL(orders.count_distinct__order_id) AS "Orders"
FROM kater_dataset.orders AS orders
GROUP BY
  KATER__BUCKET(orders.amount, [50, 100, 200])
ORDER BY
  KATER__MIN(orders.amount)
```

| Amount Band | Orders |
| --- | --- |
| < 50 | 690 |
| 50 – 100 | 854 |
| 100 – 200 | 592 |
| ≥ 200 | 833 |
| null | 31 |

## Compiles to

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

```sql
CASE
  WHEN orders.amount IS NULL
  THEN NULL
  WHEN orders.amount < 50
  THEN '< 50'
  WHEN orders.amount < 100
  THEN '50 – 100'
  WHEN orders.amount < 200
  THEN '100 – 200'
  ELSE '≥ 200'
END
```

## See also

- [Groups and flags](https://kater.ai/docs/model/fields/dimensions/groups-and-flags/)
- Other functions in [Derive](https://kater.ai/docs/sql-reference/functions/derive/): [`KATER__MAP`](https://kater.ai/docs/sql-reference/functions/kater__map/), [`KATER__PERIODS_SINCE`](https://kater.ai/docs/sql-reference/functions/kater__periods_since/)

On this page

Syntax
Arguments
Returns
Usage notes
Example
Compiles to
See also

Kater SQL Functions Derive

KATER__BUCKET

A label for the band of numbers that each value is in.

Family: Derive

Syntax

1
KATER__BUCKET(field, edges [, labels => [label, ...]])

Arguments

Required

Argument	Description
`field`	The number to put in bands.
`edges`	The numbers where one band stops and the next starts, from the lowest to the highest. A list such as `[10, 100]`.

Optional

Argument	Description
`labels`	A label for each band, from the lowest band up. Give one more label than edges. A list such as `['Small', 'Large']`.

Returns

Text, or NULL where the field is empty.

Usage notes

Each band includes its lower edge. With [25, 100], 25 is in the band 25 – 100, and 100 is in ≥ 100.
Without labels, the bands are named < 25, 25 – 100 and ≥ 100.

Example

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

1
SELECT
2
  KATER__BUCKET(orders.amount, [50, 100, 200]) AS "Amount Band",
3
  EVAL(orders.count_distinct__order_id) AS "Orders"
4
FROM kater_dataset.orders AS orders
5
GROUP BY
6
  KATER__BUCKET(orders.amount, [50, 100, 200])
7
ORDER BY
8
  KATER__MIN(orders.amount)

Amount Band	Orders
< 50	690
50 – 100	854
100 – 200	592
≥ 200	833
null	31

Compiles to

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

1
CASE
2
  WHEN orders.amount IS NULL
3
  THEN NULL
4
  WHEN orders.amount < 50
5
  THEN '< 50'
6
  WHEN orders.amount < 100
7
  THEN '50 – 100'
8
  WHEN orders.amount < 200
9
  THEN '100 – 200'
10
  ELSE '≥ 200'
11
END

See also

Groups and flags
Other functions in Derive: KATER__MAP, KATER__PERIODS_SINCE