SQL Subqueries & CTEs

0. The Sample Tables

We continue with the familiar customers and transactions tables.

1. What Is a Subquery?

A subquery (or "nested query") is a query written inside another query. It returns a result that the outer query then uses. Subqueries can appear in the SELECT, WHERE, or FROM clause.

In WHERE — filter using another query's result

-- Customers whose balance is above the overall average
SELECT customer_name, balance
FROM customers
WHERE balance > (
    SELECT AVG(balance)
    FROM customers
);

In SELECT — a scalar (single-value) subquery

-- Show each customer's transaction count as a column
SELECT c.customer_name,
       (SELECT COUNT(*)
        FROM transactions t
        WHERE t.customer_id = c.customer_id) AS txn_count
FROM customers c;

2. Correlated vs Non-Correlated Subqueries

This distinction is a favourite interview question. There are exactly two kinds:

Non-correlated (runs once)

-- The inner query runs once and returns a single average
SELECT customer_name, balance
FROM customers
WHERE balance > (SELECT AVG(balance) FROM customers);

Correlated (runs once per row)

-- Transactions above THAT customer's own average amount
SELECT t.txn_id, t.customer_id, t.amount
FROM transactions t
WHERE t.amount > (
    SELECT AVG(t2.amount)
    FROM transactions t2
    WHERE t2.customer_id = t.customer_id   -- references outer 't'
);

3. The Classic: Second-Highest Value

"Find the second-highest value" is one of the most-recycled SQL interview problems, and the cleanest solution uses a subquery.

-- Second-highest balance: highest value that is below the max
SELECT MAX(balance) AS second_highest
FROM customers
WHERE balance < (SELECT MAX(balance) FROM customers);

The inner query finds the maximum (120000). The outer query finds the largest value below that — which is 95000 (the second highest).

4. Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with the WITH clause. You can reference it like a table in the same query — which makes multi-step logic far more readable.

-- Basic CTE: high-balance customers
WITH high_balance AS (
    SELECT customer_name, balance
    FROM customers
    WHERE balance > 40000
)
SELECT *
FROM high_balance
ORDER BY balance DESC;

CTE + aggregation (a common interview pattern)

-- Average number of transactions per customer
WITH txn_counts AS (
    SELECT customer_id, COUNT(*) AS no_of_txn
    FROM transactions
    GROUP BY customer_id
)
SELECT AVG(no_of_txn) AS avg_txn_per_customer
FROM txn_counts;

Multiple CTEs, chained

WITH txn_counts AS (
         SELECT customer_id, COUNT(*) AS no_of_txn
         FROM transactions GROUP BY customer_id
     ),
     active AS (
         SELECT customer_id FROM txn_counts WHERE no_of_txn > 1
     )
SELECT c.customer_name
FROM customers c
JOIN active a ON c.customer_id = a.customer_id;

5. CTE vs Subquery vs Join — When to Use Which

Recursive CTEs (conceptual)

A recursive CTE (WITH RECURSIVE) references itself to walk hierarchical data — e.g., finding all employees under a given manager, or a category tree. You don't need to write one from memory as a fresher, but knowing the concept exists is a strong signal.