How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.
Window queries are elegant, but they can become sort-heavy and memory-heavy.
EXPLAIN ANALYZE
SELECT customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;Look for large sorts, temp files, repartition steps, or repeated scans depending on the database.
WITH recent_orders AS (
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT customer_id,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM recent_orders;Filtering early is one of the highest-leverage ways to speed up analytic queries.
Help the database read rows in the right order.
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);An index that aligns with `PARTITION BY` and `ORDER BY` can reduce expensive sorts or improve access patterns.
Break a huge query into smaller stages.
CREATE TEMP TABLE recent_orders AS
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';Materialization can help when a complex join or filter should not be recomputed before the window stage.
Portable SQL is possible, but syntax and feature support still differ.
Filter window results without wrapping a subquery.
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) = 1;`QUALIFY` exists in engines like BigQuery and Snowflake, but not in every relational database.
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;This pattern works across most SQL databases that support window functions.
Default frames can make LAST_VALUE look broken.
LAST_VALUE(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)Add the full frame when you really want the final value in the partition rather than the current-row frame result.
Different engines may sort NULLs differently.
SELECT *,
ROW_NUMBER() OVER (
ORDER BY completed_at NULLS LAST, id
) AS rn
FROM tasks;Explicit NULL ordering can preserve predictable ranking and lead/lag behavior across dialects that support it.