Subquery & CTE — Viết query phức tạp dễ đọc

Subquery

Query lồng trong query:

-- Tìm users có order lớn hơn trung bình
SELECT name, email
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE total > (SELECT AVG(total) FROM orders)
);

CTE — Common Table Expression

WITH clause giúp query phức tạp dễ đọc hơn:

WITH monthly_revenue AS (
    SELECT
        user_id,
        DATE_TRUNC('month', created_at) as month,
        SUM(total) as revenue
    FROM orders
    GROUP BY user_id, DATE_TRUNC('month', created_at)
),
top_customers AS (
    SELECT user_id, AVG(revenue) as avg_monthly
    FROM monthly_revenue
    GROUP BY user_id
    HAVING AVG(revenue) > 1000
)
SELECT u.name, u.email, t.avg_monthly
FROM users u
JOIN top_customers t ON u.id = t.user_id
ORDER BY t.avg_monthly DESC;

Window Functions

Tính toán trên "window" của rows mà không collapse rows:

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

Key insight: CTE giúp tách query phức tạp thành từng bước logic. Mỗi CTE là một "named temporary result". Debug từng CTE riêng lẻ trước khi ghép lại.