E-commerce Order Analysis Dashboard

basic

An e-commerce company needs a dashboard showing each order with contextual analytics: individual order details, running revenue totals, and customer purchase patterns.

-- E-commerce order analysis with window functions

SELECT

order_id,

customer_id,

order_date,

amount,

-- Overall business metrics

COUNT(*) OVER() as total_orders,

AVG(amount) OVER() as avg_order_value,

-- Running business totals

SUM(amount) OVER(ORDER BY order_date) as running_revenue,

ROW_NUMBER() OVER(ORDER BY order_date) as order_sequence,

-- Customer-specific insights

COUNT(*) OVER(PARTITION BY customer_id) as customer_total_orders,

SUM(amount) OVER(PARTITION BY customer_id) as customer_lifetime_value,

ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) as customer_order_number,

-- Comparative analysis

amount - AVG(amount) OVER() as vs_average_order,

CASE

WHEN amount > AVG(amount) OVER() THEN 'Above Average'

ELSE 'Below Average'

END as order_performance

FROM orders

WHERE order_date >= '2024-01-01'

ORDER BY order_date;

order_id | customer_id | order_date | amount | total_orders | avg_order_value | running_revenue | order_sequence | customer_total_orders | customer_lifetime_value | customer_order_number | vs_average_order | order_performance

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

1001 | C123 | 2024-01-15 | 150.00 | 1000 | 125.50 | 150.00 | 1 | 3 | 275.00 | 1 | 24.50 | Above Average

1002 | C456 | 2024-01-16 | 200.00 | 1000 | 125.50 | 350.00 | 2 | 1 | 200.00 | 1 | 74.50 | Above Average

1003 | C123 | 2024-01-17 | 75.00 | 1000 | 125.50 | 425.00 | 3 | 3 | 275.00 | 2 | -50.50 | Below Average

This query demonstrates the power of window functions for analytics dashboards. Each row preserves the original order details while adding rich contextual information. The running_revenue shows cumulative business performance, customer metrics reveal purchasing patterns, and comparative analysis identifies high/low-value orders. This would require multiple complex queries with GROUP BY, but window functions do it all in one efficient query.

All

With proper indexing on order_date and customer_id, this query processes 1M orders in under 3 seconds. The equivalent solution using correlated subqueries would take 45+ seconds.

flowchart LR

A["Raw Orders Data"] --> B["Window Functions"]

B --> C["Enhanced Analytics View"]

subgraph "Input Data"