Open navigation menu

CTE for Readable Complex Queries

Uses Common Table Expressions (CTEs) to break complex queries into named, readable steps for multi-level data transformation and aggregation.

SqlData Fetching

Code

snippet
WITH high_value_orders AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.order_date
    HAVING SUM(oi.quantity * oi.unit_price) > 1000
),
customer_stats AS (
    SELECT
        hvo.customer_id,
        COUNT(*) AS high_value_count,
        AVG(hvo.order_total) AS avg_high_value,
        MAX(hvo.order_total) AS max_order
    FROM high_value_orders hvo
    GROUP BY hvo.customer_id
)
SELECT
    c.first_name,
    c.last_name,
    cs.high_value_count,
    cs.avg_high_value,
    cs.max_order
FROM customer_stats cs
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.high_value_count DESC, cs.avg_high_value DESC;

Related Examples