-- Using CTE with correlated subquery for running total

WITH running_totals AS (

SELECT

order_date,

amount,

(SELECT SUM(amount)

FROM orders o2

WHERE o2.order_date <= o1.order_date

) as running_total

FROM orders o1

)

SELECT * FROM running_totals;

-- Performance: 45 seconds for 1M rows

-- Complexity: O(N²) - scans all previous rows for each row

-- Use window function for running total

SELECT

order_date,

amount,

SUM(amount) OVER (ORDER BY order_date) as running_total

FROM orders;

-- Performance: 0.4 seconds for 1M rows

-- Complexity: O(N log N) - sorts once, accumulates in single pass

Correlated subqueries for running totals are O(N²) - for each row, they scan all previous rows. Window functions are specifically optimized for this with O(N log N) complexity. The database sorts data once, then accumulates values in a single pass. For 1M rows, correlated subquery does 500 billion comparisons vs window function doing 20 million. Always use window functions for running totals, cumulative sums, and moving averages.

Use SUM() OVER (ORDER BY ...) for running totals. Never use correlated subqueries.

Critical

Query takes 45 seconds instead of 0.4 seconds. 100x slower!

-- Using CTE with COUNT subquery for ranking

WITH ranked_employees AS (

SELECT

employee_id,

name,

department,

salary,

(SELECT COUNT(*)

FROM employees e2

WHERE e2.department = e1.department

AND e2.salary > e1.salary

) + 1 as dept_rank

FROM employees e1

)