Running Total: Window Function vs CTE (Performance Comparison)

intermediate

Calculate cumulative sales over time. Compare window function approach vs CTE with correlated subquery.

-- FAST: Window function (0.4 seconds for 1M rows)

SELECT

order_date,

order_id,

amount,

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

FROM orders

ORDER BY order_date, order_id;

-- SLOW: CTE with correlated subquery (45 seconds for 1M rows)

WITH running_totals AS (

SELECT

order_date,

order_id,

amount,

(SELECT SUM(amount)

FROM orders o2

WHERE o2.order_date < o1.order_date

OR (o2.order_date = o1.order_date AND o2.order_id <= o1.order_id)

) as running_total

FROM orders o1

)

SELECT * FROM running_totals

ORDER BY order_date, order_id;

-- Why window function is 100x faster:

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

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

order_date | order_id | amount | running_total

------------+----------+--------+--------------

2024-01-01 | 1001 | 100.00 | 100.00

2024-01-01 | 1002 | 150.00 | 250.00

2024-01-02 | 1003 | 200.00 | 450.00

2024-01-02 | 1004 | 75.00 | 525.00

2024-01-03 | 1005 | 300.00 | 825.00

Performance (1M rows):

- Window function: 0.4 seconds

- CTE with correlated subquery: 45 seconds

- Speedup: 100x faster with window function