SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk FROM employees WHERE rnk <= 3;

WITH ranked AS (SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk FROM employees) SELECT * FROM ranked WHERE rnk <= 3;

Window functions are calculated after WHERE clause processing. You must use a CTE or subquery to filter on window function results. This is the most common mistake when implementing top-N patterns.

Always use CTEs or subqueries to filter window function results

High

Cannot use window function results directly in WHERE clause

LAG(order_date) OVER (ORDER BY order_date) -- Missing PARTITION BY for customer analysis

LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) -- Proper partitioning

Gap detection patterns need proper partitioning to analyze sequences within logical groups. Without PARTITION BY customer_id, you're comparing dates across different customers, which gives meaningless results.

Always partition by the logical grouping column in sequence analysis patterns

High

Incorrect gap analysis across different customers

RANK() OVER (ORDER BY salary) -- No tie-breaking logic for identical salaries

ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) -- Deterministic ranking with tie-breaker

RANK() gives the same rank to tied values, which might not be desired. ROW_NUMBER() with a tie-breaker column ensures consistent, unique rankings. Choose the ranking function based on how you want to handle ties.

Use ROW_NUMBER() with tie-breakers for unique rankings, RANK() when ties should have same rank

Medium

Non-deterministic results when values are tied

Complex nested pattern: RANK() OVER (ORDER BY (salary + AVG(salary) OVER (PARTITION BY dept)) DESC)

WITH dept_stats AS (SELECT *, AVG(salary) OVER (PARTITION BY dept) as dept_avg FROM employees) SELECT *, RANK() OVER (ORDER BY (salary + dept_avg) DESC) as composite_rank FROM dept_stats;

Nested window functions create complex, hard-to-debug queries. Breaking calculations into CTEs makes the logic clear, maintainable, and often more efficient for the query optimizer.

Use CTEs to break complex patterns into logical steps

Medium

Unreadable, unmaintainable, and potentially inefficient query

SUM(amount) OVER (PARTITION BY customer_id ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- Using RANGE unnecessarily

SUM(amount) OVER (PARTITION BY customer_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- Use ROWS for running totals

RANGE frames check for tied ORDER BY values, adding overhead. For running totals and most patterns, ROWS frames are faster and more predictable. Use RANGE only when you specifically need to group tied values.

Use ROWS frames for better performance unless you specifically need RANGE behavior

Medium

20-40% slower performance due to unnecessary tie checking

Implementing patterns without proper indexes on large tables

CREATE INDEX idx_table_partition_order ON table(partition_col, order_col); -- Then run pattern queries

Window function patterns require efficient access to partitioned and sorted data. Without proper indexes, queries perform full table scans and expensive sorts, making them unusable on large datasets.

Always create composite indexes on (PARTITION BY columns, ORDER BY columns) before implementing patterns

Critical

Extremely slow performance, full table scans, query timeouts