> ## 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.

# PERCENTILE

> Returns exact interpolated quantiles of non-NULL numeric values.

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

NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.

## 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"}}
percentile(value, percentile [, frequency])
```

### Window

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

* Sliding ROWS frames work for unweighted calls without DISTINCT. Weighted windows must start at the first row in the group.
* RANGE frames with a numeric or time offset are not supported.

## Arguments

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Values to order. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| percentile | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s), or ARRAY\<DOUBLE> | Yes | A constant fraction in \[0, 1], or a constant array of fractions. Numeric fractions are converted to DOUBLE. Use a scalar fraction when requesting a single percentile: an array containing one fraction currently raises a result-type error. |
| frequency | nonnegative INTEGER | No | Row multiplicity; zero contributes no rows and negative values raise an error. Default: 1. |

## Returns

* Type: A scalar fraction returns DOUBLE for integer inputs, or the input’s effective FLOAT, DOUBLE, or DECIMAL type for floating-point and decimal inputs. An array of fractions returns an ARRAY of the corresponding result type.
* NULL behavior: NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.
* Empty input: Returns NULL on empty input.
* Shape: Scalar fraction gives scalar; array fractions give matching-length array.
* Ordering: Array results follow requested fraction order. DESC reverses percentile orientation.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Floating-point calculations can differ slightly because of rounding.) |
| Supports DISTINCT | Partial (Removes duplicates in grouped aggregation; window calls still include duplicates.) |
| Supports window usage | Yes |
| Supports window frames | Partial (Sliding ROWS frames require an unweighted call without DISTINCT.) |
| Exact result | Partial (Floating-point arithmetic can round.) |

## Usage notes

* Exact computation retains values and memory grows with input size.
* Integer values are converted to DOUBLE. Large integers can lose precision.
* Only PERCENTILE has frequency weighting.
* NULL value/frequency pairs are skipped. Frequency zero contributes no rows; negative frequencies raise an error.
* 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.
* DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.
* Window calls accept the value, fraction, and optional frequency arguments shown in Syntax.

## Examples

For the values 0, 1, 2 and 10, percentile 0.5 lies halfway between 1 and 2, giving 1.5.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (0), (1), (2), (10)) SELECT percentile(v, 0.5) AS result FROM t;
```

| result |
| - |
| 1.5 |

### Calculate several percentiles with separate calls

To calculate several percentiles, use a separate call for each fraction:

Each fraction selects a position in the sorted values. The function interpolates between neighboring values when needed.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (0), (1), (2), (10)) SELECT percentile(v, 0.5) AS p50, percentile(v, 0.4) AS p40, percentile(v, 0.1) AS p10 FROM t;
```

| p50 | p40 | p10 |
| - | - | - |
| 1.5 | 1.2 | 0.3 |

### Request multiple percentiles as an array

The result follows the order of the requested fractions. For a single percentile, pass a scalar fraction instead of a one-element array.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (0), (1), (2), (10)) SELECT percentile(v, array(0.5, 0.4, 0.1)) AS result FROM t;
```

| result |
| - |
| \[1.5,1.2,0.3] |

## Limitations

* Floating-point and decimal results keep their effective input type, even when query tools report DOUBLE or ARRAY\<DOUBLE>. The reported type does not convert the result.
* Fractions must be constant expressions; they cannot vary by row.
* An array containing exactly one fraction currently raises a result-type error. Pass a scalar fraction for one percentile, or use separate scalar calls when selecting several percentiles.
* DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required.


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