---
title: Window Functions Quick Reference
description: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, CUME_DIST, named windows, frame specs, ORDER BY aggregate
build: default (no feature flag required)
---

# Window Functions Quick Reference


---

## UVP

HeliosDB Nano runs the PostgreSQL window functions unchanged: `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `CUME_DIST`, `PERCENT_RANK`, `NTILE`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`, plus any aggregate as a window function. Frame specs (`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, `RANGE BETWEEN n PRECEDING AND m FOLLOWING`) and named windows (`WINDOW w AS (…)`) with inheritance all work. **Same SQL as PostgreSQL — no rewrites, no flags.**

Nano supports named windows with inheritance and ORDER BY aggregate sorting (sort by `SUM(x)` without nesting it in a CTE). Implementation is `src/sql/executor/window.rs`; 38 regression tests are checked into `tests/window_functions_hardening.rs`.

---

## The Functions

### Ranking

```sql
SELECT
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense,
  PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS pct,
  CUME_DIST()  OVER (PARTITION BY dept ORDER BY salary DESC) AS cd,
  NTILE(4)     OVER (PARTITION BY dept ORDER BY salary DESC) AS quartile
FROM employees;
```

| Function | Returns | Tie behavior |
|---|---|---|
| `ROW_NUMBER()` | Sequential 1, 2, 3, … per partition | Always unique |
| `RANK()` | Rank with gaps for ties (1, 1, 3, 4) | Same value → same rank, gap |
| `DENSE_RANK()` | Rank without gaps (1, 1, 2, 3) | Same value → same rank, no gap |
| `PERCENT_RANK()` | `(rank - 1) / (total - 1)` | Range `[0, 1]` |
| `CUME_DIST()` | Cumulative distribution `[0, 1]` | Uses ORDER BY keys |
| `NTILE(n)` | Bucket number `1..n` | Equal-sized buckets, remainder in early buckets |

### Offset

```sql
SELECT
  LAG(price, 1)   OVER (ORDER BY ts) AS prev_price,
  LAG(price, 1, 0) OVER (ORDER BY ts) AS prev_price_or_zero,
  LEAD(price, 1)  OVER (ORDER BY ts) AS next_price,
  price - LAG(price) OVER (ORDER BY ts) AS delta
FROM ticks;
```

`LAG` / `LEAD` accept `(expr, offset, default)`. Default `offset` is 1, default `default` is `NULL`.

### Frame Lookup

```sql
SELECT
  FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS open,
  LAST_VALUE(price)  OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
  NTH_VALUE(price, 3) OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third
FROM ticks;
```

The frame spec **matters** for `LAST_VALUE` — without `UNBOUNDED FOLLOWING`, you'll get the current row (because the default frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`).

### Aggregates as Windows

Any aggregate works in `OVER (…)`:

```sql
SELECT
  ts, price,
  SUM(price)   OVER (PARTITION BY symbol ORDER BY ts) AS running_total,
  AVG(price)   OVER (PARTITION BY symbol ORDER BY ts
                     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma_7,
  COUNT(*)     OVER (PARTITION BY symbol)             AS partition_size,
  STDDEV(price) OVER (PARTITION BY symbol ORDER BY ts) AS rolling_stddev
FROM ticks;
```

`SUM` / `AVG` / `MIN` / `MAX` / `COUNT` / `STDDEV` / `VARIANCE` all work. NULLs propagate per SQL three-valued logic (e.g., all-NULL `SUM` returns NULL).

---

## Frame Specs

The full surface (`src/sql/executor/window.rs::compute_frame_bounds`):

```sql
-- ROWS frames (physical row count)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW       -- running aggregate
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- whole partition
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW               -- last 4 rows
ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING               -- next 6 rows
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING               -- centered window of 5

-- RANGE frames (value-based)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- default with ORDER BY
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- default without ORDER BY
```

Default frame:
- **With ORDER BY, no frame clause:** `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` (running aggregate).
- **Without ORDER BY, no frame clause:** `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` (whole partition).

This is why `LAST_VALUE` with no frame returns the current row, not the partition's last — same as PostgreSQL.

---

## Named Windows With Inheritance

Repeating `OVER (PARTITION BY symbol ORDER BY ts)` ten times is noisy. Define it once:

```sql
SELECT
  ts, price,
  SUM(price)        OVER w AS running_total,
  AVG(price)        OVER w AS running_avg,
  ROW_NUMBER()      OVER w AS rn,
  FIRST_VALUE(price) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS open
FROM ticks
WINDOW w AS (PARTITION BY symbol ORDER BY ts);
```

The fourth column **inherits** `w` and adds a frame clause — same partitioning and ordering, narrower frame. The planner handles this in `populate_named_windows` (`src/sql/planner.rs`).

You can also chain windows:

```sql
WINDOW
  w1 AS (PARTITION BY symbol),
  w2 AS (w1 ORDER BY ts),
  w3 AS (w2 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
```

---

## ORDER BY Aggregate Sorting

This now works without a CTE wrap:

```sql
SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY dept
ORDER BY SUM(salary) DESC;     -- sort by aggregate directly
```

The planner rewrites the ORDER BY aggregate reference to the column alias produced by the `HashAggregate` operator (`Self::rewrite_order_by_aggregates`, `src/sql/planner.rs`). You can also write `ORDER BY total DESC` or `ORDER BY 2 DESC` (ordinal positions are SQL-92).

---

## Recipes

### Top-N per group

```sql
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;
```

### Running totals + percentages

```sql
SELECT
  ts, amount,
  SUM(amount) OVER (ORDER BY ts) AS cumulative,
  amount * 1.0 / SUM(amount) OVER () AS pct_of_total
FROM transactions
ORDER BY ts;
```

### Gap detection (LAG)

```sql
SELECT ts, value, ts - LAG(ts) OVER (ORDER BY ts) AS gap
FROM heartbeats;
```

### Quartiles

```sql
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spend_quartile
FROM customer_summary;
```

### Moving averages

```sql
SELECT
  ts, price,
  AVG(price) OVER w_7  AS sma_7,
  AVG(price) OVER w_30 AS sma_30
FROM ticks
WINDOW
  w_7  AS (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 6  PRECEDING AND CURRENT ROW),
  w_30 AS (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 29 PRECEDING AND CURRENT ROW);
```

---

## Common Pitfalls

| Pitfall | Symptom | Fix |
|---|---|---|
| `LAST_VALUE` returns current row | Frame defaults to `… AND CURRENT ROW` | Add `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` |
| `RANK()` with no ORDER BY | All rows rank 1 | Add ORDER BY (no order = no rank semantics) |
| `SUM` over all-NULL returns 0 | Bug in older releases | Fixed — now returns NULL per SQL semantics |
| `CUME_DIST()` always 1.0 | Bug in older releases: used empty args | Fixed — now uses ORDER BY keys |
| Filtering on window result in WHERE | `WHERE rn <= 3` doesn't work | Wrap in subquery or use QUALIFY (not yet) |
| `OVER w` typo (`WINDOW w AS (…)` missing) | Parse error | Define every named window in the `WINDOW` clause |

---

## Where Next

- [EXPLAIN_AND_OPTIMIZATION_TUTORIAL](https://www.heliosdb.com/docs/nano/guides/tutorials/explain_and_optimization_tutorial/) — read window plans, see partition cost.
- [KEYSET_PAGINATION_QUICKREF](https://www.heliosdb.com/docs/nano/guides/quickstart/keyset_pagination_quickref/) — `ROW_NUMBER` + keyset for stable cursors.
- [UPSERT_QUICKREF](https://www.heliosdb.com/docs/nano/guides/quickstart/upsert_quickref/) — `ON CONFLICT` patterns for write paths.

---

*See the [changelog](https://www.heliosdb.com/docs/nano/releases/changelog/) for the release history of window-function support.*