> ## Content Index
> Fetch the complete content index at: https://blogs.originchaindb.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Window functions, correlated subqueries
- URL: https://blogs.originchaindb.com/window-functions-correlated-subqueries-590-tests/
- Published: 2026-06-06T10:00:00.000Z
- Updated: 2026-11-07T16:42:04.000Z
- Description: OriginChainDB's SQL surface now has ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and aggregate OVER, plus correlated EXISTS, IN and scalar subqueries.
- Author: OriginChainDB Team
- Tags: Engineering, sql, window-functions, correlated-subqueries, planner, #migrated-website, #byline-originchaindb-engineering

**TL;DR — OriginChainDB’s SQL surface just grew window functions and correlated subqueries: ROW\_NUMBER, RANK, DENSE\_RANK, LAG, LEAD and SUM/AVG/COUNT/MIN/MAX OVER, plus correlated EXISTS, IN and scalar subqueries. Both are first-class on the planner. Running aggregates are limited to the cumulative frame — an explicit `ROWS BETWEEN` is refused with a hint and queued for v2.**

Two SQL surfaces landed in the engine this cycle. They’re the kind of features that sound boring on a roadmap line and matter the moment a customer pastes a real analytics query into our SQL endpoint. Around 590 tests across the SQL translator, the query executor, and the HTTP layer now run on every push — the surface is large enough that we stopped trusting cursory review and started leaning on the suite.

## What works in v1

Six window function families. Every one of them runs against a plain single-table SELECT, with PARTITION BY and ORDER BY inside the OVER clause:

- `ROW_NUMBER() OVER (...)` — per-partition counter, deterministic by ORDER BY
- `RANK() OVER (...)` — competition ranking (gaps on ties)
- `DENSE_RANK() OVER (...)` — dense ranking (no gaps)
- `LAG(col [, offset [, default]]) OVER (...)` — peek backwards in the partition
- `LEAD(col [, offset [, default]]) OVER (...)` — peek forwards
- `SUM / AVG / COUNT / MIN / MAX (col) OVER (...)` — running aggregates over the partition

Correlated subqueries cover the three shapes that show up in real customer SQL:

- `EXISTS (SELECT ... WHERE inner.k = outer.k)` — including the `NOT EXISTS` negation, rewritten to a semi-join
- `WHERE col IN (SELECT ... WHERE inner.k = outer.k)` — correlated IN
- Scalar `(SELECT col FROM ... WHERE inner.k = outer.k) = value` — correlated scalar predicate

The translator detects correlation by looking at which columns the inner SELECT references. Uncorrelated subqueries take a fast path that materializes once; correlated subqueries fall through to a per-outer-row execution that the planner stitches into the predicate tree.

## A worked example

The canonical “most expensive order per customer” query, the way you’d write it against any real OLTP database:

```
SELECT customer_id, order_id, total_cents
FROM (
  SELECT
    customer_id,
    order_id,
    total_cents,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY total_cents DESC, order_id
    ) AS rn
  FROM orders
)
WHERE rn = 1;
```

This runs against OriginChainDB today, against our SQL endpoint, with the same shape you’d send to Postgres. The planner turns the inner query into:

```
Scan(orders)
  -> Window(ROW_NUMBER, partition_by=[customer_id], order_by=[total_cents DESC, order_id])
  -> ProjectAliased(customer_id, order_id, total_cents, rn=row_number)
```

The Window operator does a per-partition counter walk in the executor; the outer filter `WHERE rn = 1` is pushed onto the projected stream. No special-cased planner hack, no recipe match — `ROW_NUMBER OVER` is just a Plan node now.

The same shape gives you “yesterday’s value alongside today’s” with LAG:

```
SELECT
  symbol,
  ts,
  price,
  LAG(price, 1, 0.0) OVER (
    PARTITION BY symbol
    ORDER BY ts
  ) AS prev_price
FROM ticks;
```

And running totals with SUM OVER:

```
SELECT
  user_id,
  event_ts,
  amount_cents,
  SUM(amount_cents) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
  ) AS lifetime_spend
FROM payments;
```

Both work. Both have tests. Both are the queries we expect customers to actually run.

## The frame trade-off

Every running aggregate in v1 runs at the **cumulative frame** — implicit `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. If you write an explicit `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`, we refuse with a clear hint rather than silently ignore the clause.

This is deliberate. The implementation cost of explicit frames is real — the executor needs a sliding-window aggregator, with the per-frame add/evict bookkeeping that comes with it. Doing that well is a quarter of engineering on its own. Shipping a partial frame implementation that quietly produces wrong numbers for `ROWS BETWEEN n PRECEDING` would be worse than refusing.

So v1 ships the cumulative frame, and v1 refuses everything else with a message that names the missing surface and tells the caller to wait for v2\. The two-line failure on an unsupported frame is a feature, not a regression.

What this means in practice:

- Running totals, running maxes, running counts: work.
- Moving averages over a sliding window: refused, v2.
- Three-day moving average over price ticks: refused, v2.

If your analytics workload is mostly cumulative aggregates and ranking, v1 covers you. If it’s heavy on sliding-window analytics, v1 doesn’t.

## What `EXISTS` looks like under the planner

The fun part of correlated subqueries is what they compile to. `EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id)` is, structurally, a semi-join: “keep the customer row if at least one matching order row exists.” The translator detects the correlation by walking the inner SELECT’s predicate tree, finds the `inner.customer_id = outer.id` binding, and emits a Semi-Join plan node rather than a per-row subquery execution.

`NOT EXISTS` becomes an Anti-Join. The same detection logic runs, and the planner inverts.

This matters because the Semi/Anti-Join path is orders of magnitude cheaper than naive per-outer-row execution. A customer table with 100k rows joined against orders with 1M rows runs as one hash-join probe, not 100k separate subquery executions.

The fallback path — when the correlation is too gnarly for the semi-join rewrite — does run the subquery once per outer row. We name this in the planner output. Subqueries in WHERE (IN / EXISTS / scalar) return 400 in PREVIEW - write the query as an explicit JOIN.

## What’s queued for v2

Five gaps we know about. We’re naming them so a reader evaluating the surface doesn’t have to guess:

- **Explicit frames** — `ROWS BETWEEN n PRECEDING AND CURRENT ROW` and friends. Required for moving averages.
- **`FILTER (WHERE ...)`** — the SQL standard filter clause on window aggregates. Currently refused with a hint.
- **Named windows** — `OVER win_name` referencing a `WINDOW win_name AS (...)` clause. Currently refused; inline the spec into each OVER call.
- **`FIRST_VALUE` / `LAST_VALUE` / `NTH_VALUE` / `NTILE` / `PERCENT_RANK` / `CUME_DIST`** — the analytic functions we didn’t ship in v1\. Tracked.
- **Window + GROUP BY / JOIN in the same SELECT** — the planner refuses this combination today. The SQL-standard answer is “window runs after aggregation” — for now, do the aggregate or join in a subquery and apply the window to the result stream.

Every refusal in the translator carries a hint that names what to do instead. We’d rather a customer see a one-line “use a subquery first” message than ship a wrong answer.

## The test count, honestly

Around 590 passing tests across the SQL-relevant components. The split:

- SQL translator: 269 tests — the translator from SQL AST to plan tree.
- Query executor: 199 tests — the executor that runs the plan tree.
- HTTP surface: 123 tests — the layer that turns `POST /v1/tenants/{tenant}/sql` into a typed response.

This is the number we trust on a green CI run. We don’t claim 100% coverage — there is no static analysis behind it — but every window-function and correlated-subquery code path has at least one positive test, one refusal test, and one round-trip test against a real executor. The refusal tests matter more than the positive ones: they’re what guarantee we don’t silently regress into a wrong answer when somebody adds a new SQL feature.

## When to use this surface

The window functions make OriginChainDB’s SQL surface usable for the analytics queries that show up in real customer workloads:

- **Personalization features** — “find each user’s most-recent N items” or “rank items per category by score.” Window functions are the right shape.
- **Cohort and ranking dashboards** — running aggregates over a partition.
- **Existence and uniqueness checks against a derived set** — `WHERE EXISTS (subquery)`. The correlated path is the natural shape and the planner now handles it.

If your workload is heavy sliding-window analytics, wait for v2\. If it’s ranking + correlated existence, ship today.

## Try it

Provision a tenant from the [quickstart](https://originchaindb.com/docs/quickstart?ref=blogs.originchaindb.com). Open the SQL endpoint. Paste a real query with `ROW_NUMBER OVER (PARTITION BY ...)` or `WHERE EXISTS (SELECT ...)`. If you hit a refusal, the hint tells you what to do instead — and the v2 list above tells you whether the surface is queued. The full supported surface is in the [SQL reference](https://originchaindb.com/docs/sql?ref=blogs.originchaindb.com).