CTEs are everywhere in production SQL. They make complex reports readable by breaking them into named steps: "first get active customers, then calculate their totals, then rank them". Used in data warehousing, analytics dashboards, financial reports, and any query where clarity matters as much as correctness.

E-commerce: Customer Lifetime Value Calculation

Marketing team needs to calculate customer lifetime value (LTV) with multiple factors: total orders, total spent, average order value, customer tenure, and segment classification. Complex calculation broken into clear steps with CTEs.

-- Multi-step LTV calculation using CTEs

WITH

-- Step 1: Calculate customer order statistics

customer_orders AS (

SELECT

c.customer_id,

c.name,

c.email,

c.join_date,

COUNT(o.order_id) AS total_orders,

COALESCE(SUM(o.total), 0) AS total_spent,

COALESCE(AVG(o.total), 0) AS avg_order_value,

MAX(o.order_date) AS last_order_date

FROM customers c

LEFT JOIN orders o ON o.customer_id = c.customer_id

WHERE c.status = 'active'

GROUP BY c.customer_id, c.name, c.email, c.join_date

),

-- Step 2: Calculate customer tenure and activity

customer_metrics AS (

SELECT

*,

EXTRACT(DAYS FROM CURRENT_DATE - join_date) AS tenure_days,

EXTRACT(DAYS FROM CURRENT_DATE - last_order_date) AS days_since_last_order,

CASE

WHEN total_orders = 0 THEN 0

ELSE total_spent / NULLIF(tenure_days, 0) * 365

END AS projected_annual_value

FROM customer_orders

),

-- Step 3: Assign customer segments

customer_segments AS (

SELECT

*,

CASE

WHEN projected_annual_value >= 10000 THEN 'Platinum'

WHEN projected_annual_value >= 5000 THEN 'Gold'