Chapter 8: Subqueries, CTEs, and Window Functions

These are the advanced SQL tools that let you write complex queries cleanly. They separate beginners from intermediate SQL users.

Subqueries

A subquery is a query nested inside another query. It can appear in WHERE, FROM, or SELECT.

Subquery in WHERE (most common)

-- Employees who earn more than the average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees in the Engineering department
SELECT name
FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE name = 'Engineering'
);

-- EXISTS: "are there any orders for this customer?"
SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Subquery in FROM (Derived Table)

-- Treat a subquery result as a temporary table
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 80000;

CTEs (Common Table Expressions)

CTEs are named subqueries defined with WITH. They make complex queries readable by breaking them into logical steps.

-- Same query as above, but readable:
WITH dept_stats AS (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.name
)
SELECT dept_name, avg_salary
FROM dept_stats
WHERE avg_salary > 80000;

Multiple CTEs

WITH
  active_users AS (
    SELECT * FROM users WHERE active = true
  ),
  recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
  )
SELECT u.name, COUNT(o.id) AS recent_order_count
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY recent_order_count DESC;

Recursive CTEs

For hierarchical/tree data (org charts, categories, file systems):

-- Find all reports under Alice (recursive org chart)
WITH RECURSIVE reports AS (
    -- Base case: start with Alice
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE name = 'Alice'

    UNION ALL

    -- Recursive case: find people who report to someone already in the result
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
)
SELECT name, depth FROM reports ORDER BY depth;

-- Result:
-- Alice   | 0  (herself)
-- Bob     | 1  (direct report)
-- Charlie | 1  (direct report)
-- Dave    | 2  (reports to Bob)

Window Functions

Window functions compute values across a set of rows without collapsing them (unlike GROUP BY which reduces rows). They're incredibly powerful for analytics.

ROW_NUMBER, RANK, DENSE_RANK

-- Rank employees by salary within each department
SELECT
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;

-- Result:
-- Alice   | 1 | 95000 | 1  ← highest in dept 1
-- Dave    | 1 | 91000 | 2
-- Charlie | 1 | 88000 | 3
-- Bob     | 2 | 72000 | 1  ← highest in dept 2

Running Totals and Moving Averages

-- Running total of sales by date
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

-- 7-day moving average
SELECT
    date,
    amount,
    AVG(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

LAG and LEAD — Access Adjacent Rows

-- Compare each month's revenue to previous month
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Window Function Syntax

Practical Example: Top-N Per Group

-- Top 3 highest-paid employees per department
WITH ranked AS (
    SELECT
        name, dept_id, salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT name, dept_id, salary
FROM ranked
WHERE rn <= 3;