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

# Window queries

> Window functions compute values over related rows while retaining a result row for each input row. The OVER clause defines the partition, ordering, and optional frame.

ReferenceApplies to: Unified engine deployments

Window functions compute values over related rows while retaining a result row for each input row. The OVER clause defines the partition, ordering, and optional frame.

## Syntax

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
function(arguments) OVER (
  [PARTITION BY expression, ...]
  [ORDER BY expression [ASC | DESC], ...]
  [ROWS | RANGE frame]
)
```

PARTITION BY restarts the calculation for each key. With no PARTITION BY, all rows belong to one partition. A window ORDER BY determines calculation order, while the query's final ORDER BY determines result presentation.

## Ranking

ROW\_NUMBER assigns a unique sequence. RANK leaves gaps after ties; DENSE\_RANK does not. Include a unique ordering key when a deterministic row sequence matters.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk, i_category, i_current_price,
       ROW_NUMBER() OVER (
         PARTITION BY i_category ORDER BY i_current_price DESC NULLS LAST, i_item_sk
       ) AS row_position
FROM demo.tpcds.item
ORDER BY i_category NULLS LAST, row_position
LIMIT 20;
```

## Explicit frames

ROWS counts physical rows. RANGE groups rows by ordering values, so peers can affect the result. A frame can use UNBOUNDED PRECEDING, a supported offset PRECEDING or FOLLOWING, CURRENT ROW, and UNBOUNDED FOLLOWING. Be explicit for cumulative or whole-partition calculations.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk, i_current_price,
       SUM(CAST(i_current_price AS DOUBLE)) OVER (
         ORDER BY i_item_sk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_price_total
FROM demo.tpcds.item
WHERE i_item_sk <= 10
ORDER BY i_item_sk;
```

## Previous and next values

LAG and LEAD read values at an offset within a partition. Their default offset is one. A value outside the partition is NULL unless a default is supplied.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk, i_current_price,
       LAG(i_current_price) OVER (ORDER BY i_item_sk) AS previous_price,
       LEAD(i_current_price) OVER (ORDER BY i_item_sk) AS next_price
FROM demo.tpcds.item
WHERE i_item_sk <= 8
ORDER BY i_item_sk;
```

Filter a window result using an outer query; see [CTEs and subqueries](https://docs.e6data.com/transformation/sql/subqueries.md). Explicit IGNORE NULLS or RESPECT NULLS treatment is rejected by Native execution when it cannot preserve that behavior. See [window functions](https://docs.e6data.com/transformation/sql/functions-window.md) for additional signatures.


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