08-subqueries-and-ctes.sql

-- ============================================================
-- SQL Masterclass — Chapter 08: Subqueries and CTEs
-- ============================================================
-- 🟡 INTERMEDIATE
--
-- In this chapter you will learn:
--   • Scalar subqueries (single value)
--   • Subqueries in WHERE (IN, EXISTS)
--   • Correlated subqueries
--   • Common Table Expressions (CTEs) with WITH
--   • Chaining multiple CTEs
--   • CTEs vs Subqueries — when to use which
-- ============================================================


-- ============================================================
-- 8.1  SCALAR SUBQUERIES — Return a single value
-- ============================================================

-- Find items priced above the overall average
SELECT
    order_id,
    product_id,
    price
FROM order_items
WHERE price > (SELECT AVG(price) FROM order_items)
ORDER BY price DESC
LIMIT 10;


-- Show each order's price vs the average
SELECT
    order_id,
    price,
    (SELECT AVG(price) FROM order_items) AS avg_price,
    price - (SELECT AVG(price) FROM order_items) AS diff_from_avg
FROM order_items
ORDER BY price DESC
LIMIT 10;


-- ============================================================
-- 8.2  SUBQUERIES IN WHERE — Filtering with lists
-- ============================================================

-- Find orders from customers in São Paulo state
SELECT
    order_id,
    order_status,
    order_purchase_timestamp
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE customer_state = 'SP'
)
LIMIT 10;


-- Find products in the top 5 highest-revenue categories
SELECT
    product_id,
    product_category_name,
    product_weight_g
FROM products
WHERE product_category_name IN (
    SELECT p.product_category_name
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_category_name IS NOT NULL
    GROUP BY p.product_category_name
    ORDER BY SUM(oi.price) DESC
    LIMIT 5
)
LIMIT 20;


-- ============================================================
-- 8.3  EXISTS — Check for existence
-- ============================================================
-- More efficient than IN for large subqueries.

-- Find customers who have placed at least one order
SELECT
    c.customer_id,
    c.customer_city,
    c.customer_state
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)
LIMIT 10;


-- Find customers who have NEVER placed an order
SELECT COUNT(*) AS customers_without_orders
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);


-- ============================================================
-- 8.4  CORRELATED SUBQUERIES
-- ============================================================
-- The subquery references columns from the outer query.
-- It runs once PER ROW of the outer query.

-- For each order, show how many items it contains
SELECT
    o.order_id,
    o.order_status,
    (SELECT COUNT(*)
     FROM order_items oi
     WHERE oi.order_id = o.order_id) AS item_count,
    (SELECT SUM(price)
     FROM order_items oi
     WHERE oi.order_id = o.order_id) AS order_total
FROM orders o
LIMIT 10;


-- Find sellers whose average item price is above the global average
SELECT
    s.seller_id,
    s.seller_city,
    s.seller_state
FROM sellers s
WHERE (
    SELECT AVG(oi.price)
    FROM order_items oi
    WHERE oi.seller_id = s.seller_id
) > (SELECT AVG(price) FROM order_items)
LIMIT 10;


-- ============================================================
-- 8.5  SUBQUERIES IN FROM — Derived tables
-- ============================================================

-- Calculate statistics on order totals
SELECT
    AVG(order_total)  AS avg_order_value,
    MIN(order_total)  AS min_order_value,
    MAX(order_total)  AS max_order_value
FROM (
    SELECT
        order_id,
        SUM(price + freight_value) AS order_total
    FROM order_items
    GROUP BY order_id
) AS order_totals;


-- ============================================================
-- 8.6  CTEs — Common Table Expressions (WITH clause)
-- ============================================================
-- CTEs are like named, reusable subqueries. Much more readable!

-- Same as above but with a CTE:
WITH order_totals AS (
    SELECT
        order_id,
        SUM(price + freight_value) AS order_total
    FROM order_items
    GROUP BY order_id
)
SELECT
    AVG(order_total)  AS avg_order_value,
    MIN(order_total)  AS min_order_value,
    MAX(order_total)  AS max_order_value
FROM order_totals;


-- Seller performance using CTE
WITH seller_metrics AS (
    SELECT
        seller_id,
        COUNT(DISTINCT order_id) AS num_orders,
        SUM(price)               AS total_revenue,
        AVG(price)               AS avg_price
    FROM order_items
    GROUP BY seller_id
)
SELECT
    s.seller_id,
    s.seller_city,
    s.seller_state,
    sm.num_orders,
    sm.total_revenue,
    sm.avg_price
FROM seller_metrics sm
JOIN sellers s ON sm.seller_id = s.seller_id
ORDER BY sm.total_revenue DESC
LIMIT 10;


-- ============================================================
-- 8.7  CHAINED CTEs — Multi-step analysis
-- ============================================================
-- You can define multiple CTEs separated by commas.

-- Analyze which states have the best delivery vs review correlation
WITH delivery_metrics AS (
    SELECT
        c.customer_state,
        COUNT(*) AS total_orders,
        AVG(
            CASE
                WHEN o.order_delivered_customer_date <= o.order_estimated_delivery_date
                THEN 1.0 ELSE 0.0
            END
        ) AS on_time_rate
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.order_status = 'delivered'
      AND o.order_delivered_customer_date IS NOT NULL
    GROUP BY c.customer_state
),
review_metrics AS (
    SELECT
        c.customer_state,
        AVG(r.review_score) AS avg_review
    FROM order_reviews r
    JOIN orders o ON r.order_id = o.order_id
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_state
)
SELECT
    d.customer_state,
    d.total_orders,
    ROUND(d.on_time_rate * 100, 1) AS on_time_pct,
    ROUND(r.avg_review, 2)         AS avg_review_score
FROM delivery_metrics d
JOIN review_metrics r ON d.customer_state = r.customer_state
WHERE d.total_orders > 100
ORDER BY d.on_time_rate DESC;


-- ============================================================
-- 8.8  COMPLEX ANALYTICS WITH CTEs
-- ============================================================

-- Customer spending tiers
WITH customer_spending AS (
    SELECT
        o.customer_id,
        COUNT(DISTINCT o.order_id) AS num_orders,
        SUM(p.payment_value)       AS total_spent
    FROM orders o
    JOIN order_payments p ON o.order_id = p.order_id
    GROUP BY o.customer_id
),
spending_tiers AS (
    SELECT
        customer_id,
        num_orders,
        total_spent,
        CASE
            WHEN total_spent >= 1000 THEN 'High Value'
            WHEN total_spent >= 300  THEN 'Medium Value'
            ELSE 'Low Value'
        END AS tier
    FROM customer_spending
)
SELECT
    tier,
    COUNT(*)            AS num_customers,
    AVG(total_spent)    AS avg_spent,
    AVG(num_orders)     AS avg_orders
FROM spending_tiers
GROUP BY tier
ORDER BY avg_spent DESC;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Using a subquery, find all orders whose total
--             payment value is above the average payment value.

-- Exercise 2: Using EXISTS, find sellers who have sold items
--             priced above 1000 BRL.

-- Exercise 3: Write a CTE that calculates the total revenue per
--             product category, then select the top 10 categories.

-- Exercise 4: Write a chained CTE analysis:
--             CTE 1: Calculate each seller's total revenue
--             CTE 2: Classify sellers as 'Top' (>10k), 'Mid' (1k-10k),
--                    'Low' (<1k)
--             Final: Count sellers in each tier


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT
    order_id,
    SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
HAVING SUM(payment_value) > (SELECT AVG(payment_value) FROM order_payments)
ORDER BY total_payment DESC
LIMIT 10;

-- Exercise 2
SELECT
    s.seller_id,
    s.seller_city,
    s.seller_state
FROM sellers s
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.seller_id = s.seller_id
      AND oi.price > 1000
)
ORDER BY s.seller_state;

-- Exercise 3
WITH category_revenue AS (
    SELECT
        p.product_category_name,
        SUM(oi.price) AS total_revenue,
        COUNT(*)      AS items_sold
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_category_name IS NOT NULL
    GROUP BY p.product_category_name
)
SELECT *
FROM category_revenue
ORDER BY total_revenue DESC
LIMIT 10;

-- Exercise 4
WITH seller_revenue AS (
    SELECT
        seller_id,
        SUM(price) AS total_revenue
    FROM order_items
    GROUP BY seller_id
),
seller_tiers AS (
    SELECT
        seller_id,
        total_revenue,
        CASE
            WHEN total_revenue >= 10000 THEN 'Top Seller'
            WHEN total_revenue >= 1000  THEN 'Mid Seller'
            ELSE 'Low Seller'
        END AS tier
    FROM seller_revenue
)
SELECT
    tier,
    COUNT(*)              AS num_sellers,
    AVG(total_revenue)    AS avg_revenue,
    SUM(total_revenue)    AS total_tier_revenue
FROM seller_tiers
GROUP BY tier
ORDER BY avg_revenue DESC;