SQL Window Functions: Performance, EXPLAIN, and Dialect Notes

SQL Window Functions•Permalink

How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Performance patterns

Inspect the plan with EXPLAIN

↗#

EXPLAIN ANALYZE
SELECT customer_id,
       order_date,
       amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
       ) AS running_total
FROM orders;

# Start with the actual execution plan.

Pre-filter rows before applying windows

↗#

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;

# Reduce the working set first.

Create a supporting index for partition/order keys

↗#

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

# Help the database read rows in the right order.

Materialize an expensive intermediate result

↗#

CREATE TEMP TABLE recent_orders AS
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';

# Break a huge query into smaller stages.

## Dialect notes

QUALIFY shortcut in some warehouses

↗#

SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY created_at DESC
) = 1;

# Filter window results without wrapping a subquery.

Portable alternative to QUALIFY

↗#

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;

# Use a subquery or CTE for broad compatibility.

Remember LAST_VALUE frame semantics

↗#

LAST_VALUE(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

# Default frames can make LAST_VALUE look broken.

Be explicit about NULL ordering when needed

↗#

SELECT *,
       ROW_NUMBER() OVER (
         ORDER BY completed_at NULLS LAST, id
       ) AS rn
FROM tasks;

# Different engines may sort NULLs differently.

More in SQL Window Functions

SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization

Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.

SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis

Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.

SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics

Positional window functions for first, last, and nth values inside ordered partitions.

SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons

Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.

SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics

Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.

SQL Window Functions: Ranking, Top N per Group, and Percent Rank

Ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST.

Recommended next

No recommendations yet.

Browse all sheets →