Window functions and CTEs are both powerful SQL features, but they solve different problems. Window functions perform calculations across rows while keeping all rows (rankings, running totals, moving averages). CTEs organize complex queries into readable chunks and enable recursion. The confusion: both can calculate aggregates, but in different ways. A running total can be done with a window function (fast, 1 line) or a CTE with correlated subquery (slow, complex). Choosing wrong approach can make queries 100x slower. Understanding when to use each is critical for performance.

Stripe: Payment Analytics Dashboard

Stripe payment analytics dashboard shows merchant transaction metrics with rankings, running totals, and moving averages. Original implementation used CTEs with correlated subqueries (45 seconds). Rewritten with window functions (0.8 seconds) - 56x faster.

Reduced dashboard load time from 45s to 0.8s (56x faster). Merchants get instant insights into payment performance. Enabled real-time analytics for 1M+ merchants. Window functions eliminated O(N²) correlated subqueries. CTE handles data prep, window functions handle analytics. Critical for merchant retention and platform scalability.

Payment analytics combining CTEs for data prep and window functions for analytics

-- Stripe payment analytics (optimized version)

WITH daily_metrics AS (

-- CTE: Aggregate transactions to daily metrics

SELECT

DATE(created_at) as date,

merchant_id,

currency,

COUNT(*) as transaction_count,

SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) as successful_count,

SUM(CASE WHEN status = 'succeeded' THEN amount ELSE 0 END) as revenue,

SUM(fee) as total_fees

FROM transactions

WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'

AND merchant_id = :merchant_id

GROUP BY 1, 2, 3

)

SELECT

date,

currency,

transaction_count,

successful_count,

revenue,

total_fees,

-- Success rate

ROUND(100.0 * successful_count / NULLIF(transaction_count, 0), 2) as success_rate,

-- 7-day moving average of revenue

ROUND(AVG(revenue) OVER (

PARTITION BY currency

ORDER BY date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

), 2) as revenue_ma_7d,

-- Running total (cumulative revenue)

SUM(revenue) OVER (

PARTITION BY currency

ORDER BY date