Spaces:
Sleeping
Sleeping
File size: 1,677 Bytes
7814c1f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- Template: Multi-Step CTEs for Complex Analysis
-- Pattern: Break complex queries into logical steps
-- Use Case: Complex calculations, data transformation pipelines
WITH
-- Step 1: Calculate customer metrics
customer_metrics AS (
SELECT
c.customer_id,
c.name,
COUNT(o.order_id) as total_orders,
SUM(o.total_amount) as total_spent,
MIN(o.order_date) as first_order_date,
MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
),
-- Step 2: Calculate overall averages
overall_stats AS (
SELECT
AVG(total_spent) as avg_customer_value,
AVG(total_orders) as avg_orders_per_customer
FROM customer_metrics
),
-- Step 3: Classify customers
customer_segments AS (
SELECT
cm.*,
os.avg_customer_value,
CASE
WHEN cm.total_spent > os.avg_customer_value * 2 THEN 'VIP'
WHEN cm.total_spent > os.avg_customer_value THEN 'High Value'
WHEN cm.total_spent > 0 THEN 'Regular'
ELSE 'Inactive'
END as segment
FROM customer_metrics cm
CROSS JOIN overall_stats os
)
-- Final output
SELECT
segment,
COUNT(*) as customer_count,
AVG(total_spent) as avg_spent,
AVG(total_orders) as avg_orders
FROM customer_segments
GROUP BY segment
ORDER BY avg_spent DESC;
-- Key Concepts:
-- - WITH clause defines multiple CTEs (Common Table Expressions)
-- - Each CTE builds on previous ones
-- - Better readability than nested subqueries
-- - Can reference previous CTEs in later ones
-- - Improves maintainability and debugging |