Dashboard Query with Multiple Chart Sections

advanced

Analytics dashboard displays 3 charts using the same expensive monthly aggregation. Without materialization, aggregation runs 3 times.

-- Without MATERIALIZED: Aggregation runs 3 times (15 seconds)

WITH monthly_stats AS (

SELECT

DATE_TRUNC('month', order_date) as month,

SUM(amount) as revenue,

COUNT(*) as order_count,

AVG(amount) as avg_order_value

FROM orders -- 50M rows

WHERE order_date >= '2024-01-01'

GROUP BY 1

)

SELECT 'High Revenue Months' as chart, *

FROM monthly_stats WHERE revenue > 1000000

UNION ALL

SELECT 'High Volume Months' as chart, *

FROM monthly_stats WHERE order_count > 50000

UNION ALL

SELECT 'High AOV Months' as chart, *

FROM monthly_stats WHERE avg_order_value > 150;

-- With MATERIALIZED: Aggregation runs once (5 seconds)

WITH monthly_stats AS MATERIALIZED (

SELECT

DATE_TRUNC('month', order_date) as month,

SUM(amount) as revenue,

COUNT(*) as order_count,

AVG(amount) as avg_order_value

FROM orders -- 50M rows

WHERE order_date >= '2024-01-01'

GROUP BY 1

)

SELECT 'High Revenue Months' as chart, *

FROM monthly_stats WHERE revenue > 1000000

UNION ALL

SELECT 'High Volume Months' as chart, *

FROM monthly_stats WHERE order_count > 50000

UNION ALL

SELECT 'High AOV Months' as chart, *