SQL Subqueries for Data Analysts

Some business questions cannot be answered in a single flat query because they depend on an intermediate result. "Which customers spent more than the average?" first needs the average, then a comparison against it. "Which orders came from our top three cities?" first needs the top three cities, then a filter. A subquery — a query nested inside another query — is how you express this "answer one question to answer the next" logic. It is a step up in analytical thinking from single-pass queries.

This tutorial assumes you are comfortable with aggregate functions and joins. It sits in the intermediate part of the SQL for analytics path.

The sample data

-- orders
order_id | customer | city      | amount
---------+----------+-----------+-------
1001     | Aarti    | Hyderabad | 1200
1002     | Bhaskar  | Hyderabad | 8500
1003     | Chitra   | Pune      | 1200
1004     | Devan    | Chennai   | 600
1005     | Esha     | Pune      | 4000
1006     | Farhan   | Hyderabad | 2100

Scalar subqueries: compare to a single value

The most common analyst subquery computes one number in the inner query and compares each row to it. "Orders above the average order value":

SELECT customer, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The inner query (SELECT AVG(amount) FROM orders) returns a single value — here about 2933 — and the outer query keeps only rows above it:

customer | amount
---------+-------
Bhaskar  | 8500
Esha     | 4000

This is impossible in one flat query, because WHERE amount > AVG(amount) is illegal — you cannot use an aggregate directly in WHERE. The subquery computes the aggregate separately, then the outer query filters against the resulting scalar. This "above/below the average" pattern is one of the most-written analyst queries.

Subqueries with IN: filter against a set

When the inner query returns a list of values, use IN. "Orders from cities whose total revenue exceeds 5000":

SELECT customer, city, amount
FROM orders
WHERE city IN (
    SELECT city
    FROM orders
    GROUP BY city
    HAVING SUM(amount) > 5000
);

The inner query returns the qualifying cities (Hyderabad, at 11800), and the outer query keeps every order from those cities. IN is the natural operator when the subquery yields a set rather than a single value.

Subqueries in FROM: derived tables

A subquery in the FROM clause acts as a temporary table you query further. To find the average of per-city totals — an "average of averages" that needs two aggregation steps:

SELECT ROUND(AVG(city_total), 2) AS avg_city_revenue
FROM (
    SELECT city, SUM(amount) AS city_total
    FROM orders
    GROUP BY city
) AS city_summary;

The inner query builds a per-city summary; the outer query averages its city_total column. Two-stage aggregations like this — aggregate, then aggregate again — always need either a derived table or a CTE.

Correlated subqueries: one per outer row

A correlated subquery references a column from the outer query, so it re-runs for each outer row. "Orders above the average for their own city":

SELECT o.customer, o.city, o.amount
FROM orders o
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.city = o.city
);

The inner query's WHERE o2.city = o.city ties it to the current outer row's city, so the comparison average is city-specific. Correlated subqueries are expressive but can be slow on large tables, because they execute once per outer row. When performance matters, a window function often replaces them more efficiently.

Practical usage

Subqueries shine for "compare each row to an aggregate" and "filter against a computed set" questions, which appear constantly in analytics: above-average orders, customers in the top revenue tier, products priced above their category mean. In production you will often refactor a tangled nest of subqueries into named CTEs for readability, or into window functions for speed, but the ability to reason in nested steps is the underlying skill.

Common mistakes

• Scalar subquery returning more than one row. Using = (subquery) when the subquery returns multiple rows errors out. Use IN for a set, or ensure the subquery truly returns one value.
• NOT IN with NULLs. If the subquery's list contains a NULL, NOT IN returns no rows at all, silently, because a comparison with NULL is unknown. Prefer NOT EXISTS or filter NULLs out first.
• Overusing correlated subqueries. They re-run per outer row and can be very slow. On large tables, a join or window function usually does the same job faster.
• Unreadable nesting. Three levels of inline subqueries are hard to debug. Once logic gets deep, name the steps with CTEs.

In interviews

Subquery questions test whether you can decompose a layered problem: "customers who spent above the overall average", "the second-highest order amount", "employees earning more than their department average". The second-highest question is a classic — one clean answer uses a subquery in WHERE to exclude the maximum, then takes the max of what remains. Interviewers also probe the NOT IN NULL trap and whether you know when a correlated subquery should be rewritten as a window function.

Where this fits in your learning path

Subqueries are the gateway to multi-step analytical logic. For readability and reuse of those steps, learn CTEs, which are named subqueries. For the "compare each row to its group" pattern that correlated subqueries handle slowly, window functions are the faster, clearer replacement. All three are core intermediate skills on the data analyst roadmap.