> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# APPROX_TOP_K

> Returns the approximate most frequent input values.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

NULL is counted as an item, not skipped. Output array is non-NULL.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_top_k(value [, k [, max_items_tracked]])
```

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_top_k(value [, k [, max_items_tracked]]) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])
```

* Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

## Arguments

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Values whose frequencies are estimated. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| k | positive INTEGER literal | No | Maximum items returned. Must be a constant argument. Default: 5. |
| max\_items\_tracked | positive INTEGER literal | No | Maximum number of distinct values tracked. Must be a constant integer, at least k and no greater than 1000000. Default: 10000. |

## Returns

* Type: ARRAY of STRUCT(item, count), where item has the SQL type of value and count is BIGINT
* NULL behavior: NULL is counted as an item, not skipped. Output array is non-NULL.
* Empty input: Returns an empty array on empty input.
* Shape: At most k item/count structs.
* Ordering: Descending estimated count; tied item order is unspecified.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Estimated counts and the order of tied items can vary between executions.) |
| Supports DISTINCT | No |
| Supports window usage | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) |
| Supports window frames | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) |
| Exact result | No |

* Approximation bound: Values occurring more than 2 × (number of input rows) / max\_items\_tracked times are retained among the tracked candidates. Only the top k candidates are returned.

## Usage notes

* Counts are exact while all distinct values fit in the available capacity. When values are discarded to stay within that capacity, counts can be lower than the true counts. The result does not include an error bound.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.

## Examples

### Basic example

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES ('a'),('a'),('a'),('b'),('b'),('c')) SELECT approx_top_k("value",2,100) AS result FROM t;
```

| result |
| - |
| \[\{"count":3,"item":"a"},\{"count":2,"item":"b"}] |

### Use the default number of results

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES ('a'), ('a'), ('b')) SELECT approx_top_k(v) AS result FROM t;
```

| result |
| - |
| \[\{"count":2,"item":"a"},\{"count":1,"item":"b"}] |

## Limitations

* Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
* DISTINCT does not remove duplicate input values in grouped calls. Removing duplicates can change the result, so remove duplicates from the input separately when needed.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.