LAST_VALUE(sales) OVER (ORDER BY date)

LAST_VALUE(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Default frame ends at current row, so LAST_VALUE sees only up to current position. This is the most common frame mistake - 80% of developers get this wrong initially.

Always specify full frame for FIRST_VALUE and LAST_VALUE functions

High

Returns current row value instead of actual last value

AVG(amount) OVER (ORDER BY date RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)

AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

RANGE 7 PRECEDING means "values 7 less than current ORDER BY value", not "7 rows before". For row-based windows, use ROWS. RANGE is for value-based grouping.

Use ROWS for physical row counting, RANGE only for value-based grouping

High

RANGE with numeric boundaries often gives unexpected results

SUM(sales) OVER (PARTITION BY region ORDER BY date) -- Relying on default frame

SUM(sales) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups ties differently than ROWS. Always specify explicitly for clarity and predictability.

Specify frames explicitly rather than relying on defaults for maintainable code

Medium

Unclear intent and potential unexpected behavior with RANGE default

AVG(price) OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) -- On 10M row table without index

CREATE INDEX idx_date ON table(date); AVG(price) OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)

Frame calculations require efficient access to ordered data. Without indexes on ORDER BY columns, the database must sort the entire dataset for each frame calculation.

Always index PARTITION BY and ORDER BY columns before using frames

High

Extremely slow performance due to lack of proper indexing

MAX(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -- On 50M row partition

MAX(sales) OVER (ORDER BY date ROWS BETWEEN 365 PRECEDING AND CURRENT ROW) -- Or use materialized view

UNBOUNDED FOLLOWING requires buffering the entire remaining partition in memory. For very large datasets, this can cause memory issues and slow performance.

Use reasonable frame limits or pre-aggregate data for very large partitions

Medium

Memory exhaustion and poor performance on very large partitions

Multiple window functions with different frames: AVG() OVER (ROWS 7 PRECEDING), SUM() OVER (ROWS 30 PRECEDING), COUNT() OVER (ROWS 90 PRECEDING)

Use named windows or group functions with similar frames together

Each different frame specification requires separate processing passes. Group functions with similar frames or use named windows to improve efficiency.

Group window functions with similar frame specifications for better performance

Medium

Inefficient processing with multiple different frame calculations