Structuring Complex SQL Queries with Non-Recursive Common Table Expressions (CTEs)

-- Create sample tables
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('Peter', 'Jones', '[email protected]');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.50),
(2, '2023-01-20', 200.00),
(1, '2023-02-01', 50.00),
(3, '2023-02-10', 300.75),
(2, '2023-03-05', 75.25),
(1, '2023-03-10', 150.00);

-- Original complex query (without CTEs)
-- SELECT
--     c.first_name,
--     c.last_name,
--     SUM(o.total_amount) AS lifetime_value,
--     COUNT(o.order_id) AS total_orders
-- FROM
--     customers c
-- JOIN
--     orders o ON c.customer_id = o.customer_id
-- WHERE
--     o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
-- GROUP BY
--     c.customer_id, c.first_name, c.last_name
-- HAVING
--     SUM(o.total_amount) > 100
-- ORDER BY
--     lifetime_value DESC;


-- Refactored query using non-recursive Common Table Expressions (CTEs)
WITH RecentOrders AS (
    -- Step 1: Filter orders within a specific date range
    SELECT
        order_id,
        customer_id,
        total_amount
    FROM
        orders
    WHERE
        order_date BETWEEN '2023-01-01' AND '2023-03-31'
),
CustomerOrderSummary AS (
    -- Step 2: Aggregate order data for each customer
    SELECT
        customer_id,
        SUM(total_amount) AS lifetime_value,
        COUNT(order_id) AS total_orders
    FROM
        RecentOrders
    GROUP BY
        customer_id
    HAVING
        SUM(total_amount) > 100
)
-- Step 3: Join with customer details and select final results
SELECT
    c.first_name,
    c.last_name,
    COS.lifetime_value,
    COS.total_orders
FROM
    customers c
JOIN
    CustomerOrderSummary COS ON c.customer_id = COS.customer_id
ORDER BY
    COS.lifetime_value DESC;