**Performance Benchmarks (1M rows):**

| Task | Window Function | CTE + Correlated Subquery | Winner |

|------|----------------|---------------------------|--------|

| Running total | 0.4s (O(N log N)) | 45s (O(N²)) | Window 100x faster |

| Ranking | 0.5s | 50s | Window 100x faster |

| Moving average | 0.6s | 60s | Window 100x faster |

| Data prep + analytics | 2s (CTE + Window) | N/A | Combined approach |

**Why Window Functions Are Faster:**

1. **Optimized algorithms**: Database engines have specialized algorithms for window functions

2. **Single pass**: Window functions often process data in one pass

3. **No correlated subqueries**: Avoid O(N²) complexity

4. **Efficient sorting**: Built-in sort optimization for ORDER BY in window

**When CTEs Are Better:**

1. **Readability**: Breaking complex query into steps

2. **Reusability**: Reference same subquery multiple times (with MATERIALIZED)

3. **Debugging**: Test each CTE independently

4. **Recursion**: Only way to handle hierarchical data

**Memory Considerations:**

- Window functions: Require sorting/partitioning (work_mem)

- CTEs: May materialize results (work_mem or temp disk)

- Combined: Both memory requirements apply

**Best Practices:**

1. **Use window functions for row-level analytics** (rankings, running totals)

2. **Use CTEs for query organization** (break complex logic)

3. **Combine both** (CTE for prep, window for analytics)

4. **Avoid correlated subqueries** when window function can do it

5. **Profile with EXPLAIN ANALYZE** to verify performance

Window functions are O(N log N), correlated subqueries are O(N²)

Running total: Window function 100x faster than CTE with correlated subquery

Ranking: Window function 100x faster than CTE with COUNT subquery

Moving average: Window function is only practical approach

Index columns used in PARTITION BY and ORDER BY

Limit window frame size when possible (ROWS BETWEEN 10 PRECEDING...)

Use ROWS instead of RANGE when possible (ROWS is faster)

Materialize CTEs when referenced multiple times

Combine window functions in single query (share sort operation)

Monitor work_mem for large window operations

Using CTE with correlated subquery instead of window function (100x slower)

Not understanding difference between RANK, DENSE_RANK, ROW_NUMBER

Forgetting ORDER BY in window function (undefined behavior)