Window functions on Elasticsearch data

Window functions run in FlatQL's engine over the rows the cluster returned for your WHERE clause. Write the filter so the cluster does the narrowing, then rank, lag and sum freely.

What runs where#

A statement with a window function is never a simple scan, so the cluster receives the pushed WHERE conjuncts and nothing else: no size, no sort. The engine holds the matching rows, at most 50,000 per index, and evaluates the window there. The pushdown rules spell out why. In practice this means one thing: the WHERE must do the narrowing, and it must use predicates the cluster can apply.

ROW_NUMBER with QUALIFY: latest row per key#

The most recent paid order of every customer:

SELECT customer_id, order_id, amount, placed_at
FROM orders
WHERE status = 'paid' AND placed_at >= '2026-07-01'
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) = 1
ORDER BY placed_at DESC;

QUALIFY filters on the window result without a subquery. The cluster received a scroll with a term on status and a range on placed_at; the engine partitioned by customer, numbered the orders newest first and kept number one. The same statement as a subquery works too:

SELECT customer_id, order_id, amount, placed_at
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.placed_at DESC) AS rn
  FROM orders o
  WHERE o.status = 'paid' AND o.placed_at >= '2026-07-01'
)
WHERE rn = 1;

Note the alias o. on every column in the inner query. With two SELECTs the statement is no longer single-table, and only qualified columns are attributed to an index for pushdown.

RANK and DENSE_RANK: top N per group#

The three best-rated suppliers in each category:

SELECT category, name, country, rating
FROM suppliers
WHERE rating IS NOT NULL
QUALIFY RANK() OVER (PARTITION BY category ORDER BY rating DESC) <= 3
ORDER BY category, rating DESC;

RANK leaves gaps after ties (1, 1, 3); DENSE_RANK does not (1, 1, 2). Use ROW_NUMBER when you want exactly three rows per category regardless of ties.

LAG and LEAD: compare with the neighbouring row#

Change in amount from each customer's previous order:

SELECT customer_id, order_id, placed_at, amount,
       LAG(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS previous_amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS change,
       LEAD(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_order_at
FROM orders
WHERE currency = 'EUR' AND placed_at >= '2026-01-01'
ORDER BY customer_id, placed_at;

The first order of each customer has a null previous_amount and therefore a null change. LAG(amount, 1, 0) substitutes a default. DATEDIFF('day', placed_at, LEAD(placed_at) OVER (...)) gives the gap to the next order in days.

SUM OVER with frames: running totals and moving averages#

Cumulative revenue per currency through the year:

SELECT currency, placed_at, amount,
       SUM(amount) OVER (
         PARTITION BY currency
         ORDER BY placed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM orders
WHERE status = 'paid' AND placed_at >= '2026-01-01'
ORDER BY currency, placed_at;

A moving average over the last ten requests of one service, from a log index pattern:

SELECT "@timestamp", region, duration_ms,
       AVG(duration_ms) OVER (
         PARTITION BY region
         ORDER BY "@timestamp"
         ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
       ) AS avg_last_10
FROM "logs-*"
WHERE service = 'checkout'
  AND "@timestamp" >= '2026-11-03T00:00:00Z' AND "@timestamp" < '2026-11-03T01:00:00Z'
ORDER BY region, "@timestamp";

Frames follow the standard: ROWS counts rows, RANGE uses the ordering value, and the default frame when you write ORDER BY without a frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The one-hour window on @timestamp is what keeps the fetch under the cap on a busy log index; widen it and watch the execution line.

Windows over aggregates#

Window functions compose with GROUP BY. Share of each country in the supplier count:

SELECT country,
       COUNT(*) AS suppliers,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_pct
FROM suppliers
WHERE category = 'logistics'
GROUP BY country
ORDER BY suppliers DESC;

The window makes the statement ineligible for GROUP BY pushdown, so the engine groups the scanned rows itself. When the grouped index is large, do the GROUP BY in a CTE first, where it can push as a composite aggregation, and apply the window to the CTE:

WITH by_country AS (
  SELECT s.country, COUNT(*) AS suppliers
  FROM suppliers s
  WHERE s.category = 'logistics'
  GROUP BY s.country
)
SELECT country, suppliers,
       ROUND(100.0 * suppliers / SUM(suppliers) OVER (), 1) AS share_pct
FROM by_country
ORDER BY suppliers DESC;

Other window functions#

FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, PERCENT_RANK, CUME_DIST, COUNT, MIN, MAX and AVG all accept an OVER clause, with the same PARTITION BY, ORDER BY and frame syntax. Name the window once with WINDOW w AS (...) and reuse it as OVER w when a statement has several functions over the same partition.

For what these patterns look like without a SQL layer, see Elasticsearch window functions. Joins and CTEs, UNION and HAVING cover the other engine-side features.