E-commerce Sales Performance Optimization

advanced

Optimize slow sales analytics query processing 10M+ orders with window functions

-- BEFORE: Unoptimized query (30+ seconds)

SELECT

order_id,

customer_id,

order_date,

total_amount,

-- Multiple window functions without proper indexing

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date DESC

) as order_sequence,

AVG(total_amount) OVER (

PARTITION BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)

ORDER BY order_date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

) as rolling_avg,

SUM(total_amount) OVER (

PARTITION BY customer_id

ORDER BY order_date

RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW

) as customer_30day_total

FROM orders

WHERE order_date >= '2023-01-01'

ORDER BY customer_id, order_date;

-- OPTIMIZATION STEP 1: Create strategic indexes

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

CREATE INDEX idx_orders_date_amount ON orders(order_date, total_amount);

CREATE INDEX idx_orders_year_month ON orders(

EXTRACT(YEAR FROM order_date),

EXTRACT(MONTH FROM order_date),

order_date

);

-- OPTIMIZATION STEP 2: Rewrite with performance improvements

WITH optimized_base AS (

SELECT

order_id,

customer_id,