Start free
← All posts

Window functions, correlated subqueries

OriginChain engineering · Jun 6, 2026
sql window-functions correlated-subqueries engineering planner

TL;DR — OriginChain’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:

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

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 OriginChain 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:

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:

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:

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 OriginChain’s SQL surface usable for the analytics queries that show up in real customer workloads:

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

← All posts Subscribe to RSS →