Subqueries — Learn Correlated, HAVING + SQ, Derived Table × JOIN, and Multi-Level Nesting in Practice

QUESTION 1

Correlated subquery — extract only each user's "latest order" from the same table

Correlated SQWHERE clauseLatest-row extractionSame-table comparison

Background ▼

A correlated subquery is a subquery whose inner query references columns of the outer query. Because the inner query runs each time the outer query processes a row, it can dynamically compute a per-row value such as "the maximum value for that row's user."

SELECT * FROM orders o1
WHERE ordered_at = (
  SELECT MAX(ordered_at)  -- compute the max for o1's user_id
  FROM   orders o2       -- reference the same table under a different alias
  WHERE  o2.user_id = o1.user_id  -- reference an outer column (this is the "correlation")
);

Difference from a non-correlated SQ: the scalar SQ in the basic edition (Q1) is evaluated only once, independently of the outer query. A correlated SQ is evaluated for each row of the outer query, so it enables row-dependent computation such as "a different maximum per user."

Problem ▼

From the orders table, retrieve only each user's latest order (the row where ordered_at is the maximum). Return order_id, user_id, amount, status, ordered_at ordered by user_id ascending.

Tables used

▸ orders

order_id	user_id	amount	status	ordered_at
101	1	8,000	completed	2024-05-01
102	1	12,000	completed	2024-05-20
103	2	3,500	completed	2024-05-10
104	3	9,500	completed	2024-05-15
105	3	11,000	completed	2024-05-25
106	4	2,000	completed	2024-05-08
107	1	15,000	completed	2024-06-10

Expected Output ▼

Expected output (only each user's latest order, ordered by user_id ascending):

order_id	user_id	amount	status	ordered_at
107	1	15,000	completed	2024-06-10
103	2	3,500	completed	2024-05-10
105	3	11,000	completed	2024-05-25
106	4	2,000	completed	2024-05-08

※ user1's latest is 107 (06-10) → 101/102 excluded. user3's latest is 105 (05-25) → 104 excluded.

Model Answer ▼

SELECT
  order_id, user_id, amount, status, ordered_at
FROM   orders o1                        -- alias o1 for the outer query
WHERE  ordered_at = (                   -- compare each row's ordered_at with the SQ result
  SELECT MAX(ordered_at)               -- find that user's maximum date
  FROM   orders o2                     -- reference the same table under alias o2 (self-reference)
  WHERE  o2.user_id = o1.user_id      -- referencing an outer column is the essence of "correlation"
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders o1    → process all 7 rows one at a time
  2. Correlated SQ     → compare with MAX(ordered_at) per row
  3. WHERE filter      → narrow to each user's latest order
  4. SELECT ...        → select the needed columns
  5. ORDER BY user_id  → user_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  order_id, user_id, amount, status, ordered_at
FROM   orders o1
WHERE  ordered_at = (
  SELECT MAX(ordered_at)
  FROM   orders o2
  WHERE  o2.user_id = o1.user_id
)
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM orders o1 (outer query) ▼

FROM orders o1Process all 7 rows of the outer query's orders table (o1) one at a time. From here, the correlated subquery is evaluated once per row.

1 / 3

o1.order_id	user_id	amount	ordered_at
101	1	8,000	2024-05-01
102	1	12,000	2024-05-20
103	2	3,500	2024-05-10
104	3	9,500	2024-05-15
105	3	11,000	2024-05-25
106	4	2,000	2024-05-08
107	1	15,000	2024-06-10

All 7 rows read

LEARNING POINTS

The essence of a correlated SQ — use the outer column inside the inner query: writing o2.user_id = o1.user_id inside makes it compute the MAX for the user_id of the row the outer query is currently processing. Because the inner query re-runs whenever the outer row changes, you get a different aggregate value per row. A non-correlated SQ is fundamentally different in that it is evaluated only once.

Self-reference: referencing the same table under two names: orders o1 (outer) and orders o2 (inner) are the same table, but they are distinguished by giving them different aliases. Without aliases, "which column is which" becomes ambiguous and raises an error.

How to think about performance: a correlated SQ repeats the inner scan for each of the N outer rows. If orders.user_id has no index, a full scan happens as many times as there are outer rows. For large data, a window function such as ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) combined with a CTE is recommended.

Caution when the same date appears multiple times: if several rows share the MAX date, multiple rows are returned. To strictly narrow to a single row, use a derived table with ORDER BY ordered_at DESC, order_id DESC LIMIT 1, or handle it with a window function plus CTE.

ANTI-PATTERNS

Using the same alias for the outer and inner query: writing FROM orders o WHERE ... = (SELECT MAX(...) FROM orders o WHERE ...) makes the inner o shadow the outer one. Always give the inner and outer queries distinct aliases (o1/o2, etc.).

Applying a correlated SQ to large data: using a correlated SQ on a table with many rows and no index causes an N×M scan and becomes seriously slow. Check the execution plan (EXPLAIN) and, if needed, consider rewriting to a window function.

Field note: comparing best practices for "getting the latest row"

A correlated SQ is simple and readable, but on PostgreSQL and MySQL 8+ a CTE using ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) is the first choice in practice. It uses indexes efficiently and lets you control same-date tie-breaks with order_id. It is worth understanding the correlated SQ as a "portable form that works on any RDBMS," so you can handle older environments and exam questions too.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

HAVING × scalar subquery — filter groups by comparing group aggregates against the overall average

HAVINGScalar SQGroup comparisonSegment analysis

Background ▼

The HAVING clause filters the result of a GROUP BY aggregation. WHERE filters before aggregation (per row); HAVING filters after aggregation (per group). By using a scalar SQ as the comparison value in the HAVING clause, you can dynamically compare an overall aggregate against a group aggregate.

SELECT col, AVG(amount)
FROM   orders
GROUP BY col
HAVING AVG(amount) > (             -- post-aggregation group condition
  SELECT AVG(amount) FROM orders  -- scalar SQ: returns the overall average
);

Choosing WHERE vs HAVING: WHERE AVG(amount) > ... is a syntax error. Because WHERE is evaluated before GROUP BY, you cannot use aggregate functions in its condition. To filter after aggregation, you must use HAVING.

Problem ▼

From the orders table, limited to orders whose status is 'completed', retrieve the users whose per-user average order amount is higher than the overall average. Return user_id, avg_amount (the ROUNDed integer) ordered by avg_amount descending.

Tables used

▸ orders

order_id	user_id	amount	status
101	1	8,000	completed
102	1	12,000	completed
103	2	3,500	completed
104	3	9,500	completed
105	3	11,000	completed
106	4	2,000	completed
107	1	15,000	completed

Expected Output ▼

Expected output (users exceeding the overall average ≈ 8,714, ordered by avg_amount descending):

user_id	avg_amount
1	11,667
3	10,250

※ Overall AVG = (8000+12000+3500+9500+11000+2000+15000)÷7 ≈ 8,714. user2 (3,500) and user4 (2,000) are below the overall average and are excluded.

Model Answer ▼

SELECT
  user_id,
  ROUND(AVG(amount)) AS avg_amount  -- ROUND to an integer for display
FROM   orders
WHERE  status = 'completed'         -- pre-aggregation filter (per row)
GROUP BY user_id                    -- group by user
HAVING AVG(amount) > (              -- post-aggregation filter (per group)
  SELECT AVG(amount)                -- scalar SQ: pre-compute the completed overall average
  FROM   orders
  WHERE  status = 'completed'       -- match the same condition as the outer query
)
ORDER BY avg_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Scalar SQ pre-evaluation    → fix the completed average as the threshold
  2. FROM orders                 → read all rows
  3. WHERE status='completed'    → narrow to completed
  4. GROUP BY user_id            → aggregate by user
  5. HAVING AVG(amount) > thresh → keep groups above the average
  6. SELECT ROUND(AVG(amount))   → output the average
  7. ORDER BY avg_amount DESC    → descending
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id,
  ROUND(AVG(amount)) AS avg_amount
FROM   orders
WHERE  status = 'completed'
GROUP BY user_id
HAVING AVG(amount) > (
  SELECT AVG(amount)
  FROM   orders
  WHERE  status = 'completed'
)
ORDER BY avg_amount DESC;

LEGEND ▼

Columns / keys under evaluation

✓ pass

① Scalar SQ (fix the overall average) ▼

SELECT AVG(amount) FROM orders WHERE status='completed'The subquery pre-computes, only once, the overall average that becomes the HAVING comparison threshold. The average over all 7 rows (about 8,714) is fixed as a constant.

1 / 5

order_id	user_id	amount	AVG target
101	1	8,000	✓ included
102	1	12,000	✓ included
103	2	3,500	✓ included
104	3	9,500	✓ included
105	3	11,000	✓ included
106	4	2,000	✓ included
107	1	15,000	✓ included

→ returns AVG(amount) ≈ 8,714

LEARNING POINTS

SQL evaluation order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY: this order is the fundamental principle. WHERE AVG(amount) > ... is evaluated before GROUP BY, so aggregate functions cannot be used there and it is a syntax error. To condition on an aggregate value, you must use HAVING.

The scalar SQ is evaluated before HAVING: the scalar SQ is evaluated only once, before the whole outer query, fixing the constant value (≈ 8,714). By the time GROUP BY → HAVING runs, this value is already fixed, so "narrowing groups by the overall average as a fixed threshold" is achieved in a single query.

Be mindful of aligning the WHERE conditions of the outer and inner queries: here both the outer and inner queries use WHERE status='completed'. Dropping the inner WHERE would make it "the overall average across all statuses," which changes the meaning. It is important to design clearly what you are comparing the average against.

A field pattern — extracting high-value user segments: segmentation queries like "customers with a higher purchase amount than the overall average" or "departments exceeding a benchmark" appear frequently in analytics dashboards and CRM. By changing the value of the scalar SQ, you can dynamically change the threshold.

ANTI-PATTERNS

Writing an aggregate function in WHERE: WHERE AVG(amount) > 8714 is a syntax error. Aggregate functions cannot be used in the WHERE clause. Always use HAVING for post-aggregation conditions. This is one of the most common pitfalls for beginners.

Failing to consciously align the outer and inner WHERE conditions: if the inner scalar SQ has no WHERE (average across all statuses), the meaning of "overall average" changes. Deliberately design whether to align or differ the inner and outer filter conditions.

Field note: choosing between HAVING and a derived table

For a simple "group aggregate vs constant" comparison, HAVING is the shortest to write. On the other hand, if you want to JOIN yet another table onto the aggregated result, or combine multiple aggregate columns into a compound condition, move to a derived table (Q4) or a CTE. In practice, "try HAVING first, and split into a CTE once it grows complex" is a natural flow.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

EXISTS + NOT EXISTS — extract "ordered but not yet reviewed" users with a compound condition

NOT EXISTSCompound EXISTSBehavioral funnelSet-difference detection

Background ▼

By combining multiple EXISTS / NOT EXISTS with AND, as in WHERE EXISTS (...) AND NOT EXISTS (...), you can extract in a single query the "rows that satisfy condition A and do not satisfy condition B." This is a field pattern that makes the EXISTS from the basic edition (Q5) compound.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE  o.user_id = u.user_id  -- an order exists
)
  AND NOT EXISTS (
  SELECT 1 FROM reviews r
  WHERE  r.user_id = u.user_id  -- and no review exists
);

NOT EXISTS is NULL-safe: NOT IN (basic edition Q3) has the trap that a single NULL in the list excludes all rows, but NOT EXISTS is unaffected by NULL. In production data, it is safer to prefer NOT EXISTS over NOT IN as a habit.

Problem ▼

From the users table, retrieve users who have at least one completed order yet have never posted a review. Return user_id, name, plan ordered by user_id ascending.

Tables used

▸ users

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	free
3	Suzuki Ichiro	premium
4	Yamada Jiro	standard
5	Ito Saburo	free

▸ orders (relevant columns only)

order_id	user_id	status
101	1	completed
102	1	completed
103	2	completed
104	3	completed
105	3	completed
106	4	completed
107	1	completed

▸ reviews

review_id	user_id	order_id	rating
1	1	101	5
2	3	104	4

Expected Output ▼

Expected output (has completed / no review, ordered by user_id ascending):

user_id	name	plan
2	Sato Hanako	free
4	Yamada Jiro	standard

※ user1 has review1 → excluded. user3 has review2 → excluded. user5 has 0 orders → EXISTS fails → excluded.

Model Answer ▼

SELECT
  u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (                   -- condition ①: has at least one completed order
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status  = 'completed'  -- completed only
)
  AND NOT EXISTS (                -- condition ②: has no review at all
  SELECT 1
  FROM   reviews r
  WHERE  r.user_id = u.user_id    -- confirm no row exists in reviews
)
ORDER BY u.user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                      → process one row at a time
  2. EXISTS (completed order)          → decide "has a completed one"
  3. AND NOT EXISTS (review absent)    → decide "has no review"
  4. SELECT u.user_id, u.name, u.plan  → select passing rows
  5. ORDER BY u.user_id                → user_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status = 'completed'
)
  AND NOT EXISTS (
  SELECT 1
  FROM   reviews r
  WHERE  r.user_id = u.user_id
)
ORDER BY u.user_id;

LEGEND ▼

Rows read / loaded

① FROM users u (read all rows) ▼

FROM users uProcess all 5 rows of the users table one at a time. For each row, the two subqueries EXISTS and NOT EXISTS are evaluated in turn.

1 / 4

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	free
3	Suzuki Ichiro	premium
4	Yamada Jiro	standard
5	Ito Saburo	free

All 5 rows read

LEARNING POINTS

Combining EXISTS + NOT EXISTS with AND: the pattern "satisfies condition A AND does not satisfy condition B" is just a matter of combining them with AND. The SQL optimizer evaluates AND conditions in an efficient order, so the written order and performance do not necessarily match. Which one is evaluated first can be checked with the execution plan (EXPLAIN).

NOT EXISTS is NULL-safe: even if NULL is present in the user_id of the reviews table, NOT EXISTS works as expected. In contrast, NOT IN (SELECT user_id FROM reviews) risks returning 0 rows because everything becomes UNKNOWN if even one NULL exists (see basic edition Q3). In practice, make NOT EXISTS your first choice.

A field pattern — detecting drop-off in a user behavior funnel: queries that find users who dropped off between steps — such as "ordered but did not review," "registered but did not set a profile," "logged in but did not finish the tutorial" — are the basis of retention initiatives. Remember this pattern and you can use it instantly in marketing batches.

The benefit of short-circuit evaluation: EXISTS returns TRUE as soon as one row is found, and NOT EXISTS returns FALSE as soon as one row is found. Since it stops scanning reviews the moment even one row exists, it is very fast when an index is effective.

ANTI-PATTERNS

Judging "no review" with NOT IN: WHERE user_id NOT IN (SELECT user_id FROM reviews) excludes all rows the moment a NULL enters reviews.user_id. Because reviews can acquire NULLs during operation, always use NOT EXISTS, or add WHERE user_id IS NOT NULL to the inner query.

Mistaking the trade-off with LEFT JOIN + IS NULL: you can get the same result with LEFT JOIN reviews ON ... WHERE reviews.user_id IS NULL, but EXISTS/NOT EXISTS is clearer in that it only "checks existence" and does not add selected columns. If you also want the joined table's columns in SELECT, use JOIN; if you only need an existence check, EXISTS expresses the intent more clearly.

Field note: comparing 3 patterns — NOT EXISTS / NOT IN / LEFT JOIN

For the set difference "take rows from table A that do not exist in table B," there are 3 ways: ① NOT EXISTS ② NOT IN ③ LEFT JOIN + IS NULL. ① is NULL-safe, its intent is clear, and it is the most recommended. ② is OK only when the list is small and certainly has no NULL. ③ is for when you also output the joined table's columns in SELECT, or when embedding into a complex query that includes OUTER JOINs. In production, defaulting to ① NOT EXISTS is the safest.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Derived table × INNER JOIN — join user info onto an aggregate summary to return a list

Derived tableINNER JOINAggregate + master joinAPI response design

Background ▼

In the basic edition Q4, a FROM-clause subquery (derived table) grouped and aggregated, and the outer WHERE filtered it. Extending this pattern further, INNER JOINing the aggregate result (derived table) with another master table to obtain aggregate values and related information in a single query is the most frequent pattern in practice.

SELECT m.name, s.total
FROM   master m
INNER JOIN (
  SELECT id, SUM(amount) AS total
  FROM   transactions
  GROUP BY id
) AS s ON m.id = s.id;  -- join the aggregate result with the master

The exclusion effect of INNER JOIN: INNER JOIN includes rows in the result only when matching rows exist in both tables. Users that do not exist on the derived-table side (users with zero orders) are automatically excluded. To include all users, use a LEFT JOIN.

Problem ▼

Aggregate the orders table to obtain each user's order count, total amount, and average amount, and combine them with the name / plan of the users table to return the result. Consider completed orders only, and return user_id, name, plan, order_count, total_amount, avg_amount ordered by total_amount descending.

Tables used

▸ users

user_id	name	plan
1	Tanaka Taro	premium
2	Sato Hanako	free
3	Suzuki Ichiro	premium
4	Yamada Jiro	standard
5	Ito Saburo	free

▸ orders

order_id	user_id	amount	status
101	1	8,000	completed
102	1	12,000	completed
103	2	3,500	completed
104	3	9,500	completed
105	3	11,000	completed
106	4	2,000	completed
107	1	15,000	completed

Expected Output ▼

Expected output (aggregated over completed orders only, ordered by total_amount descending):

user_id	name	plan	order_count	total_amount	avg_amount
1	Tanaka Taro	premium	3	35,000	11,667
3	Suzuki Ichiro	premium	2	20,500	10,250
2	Sato Hanako	free	1	3,500	3,500
4	Yamada Jiro	standard	1	2,000	2,000

※ user5 has no completed orders, so no row is generated in the derived table and it is automatically excluded by the INNER JOIN.

Model Answer ▼

SELECT
  u.user_id,
  u.name,
  u.plan,
  s.order_count,
  s.total_amount,
  s.avg_amount
FROM   users u
INNER JOIN (                          -- INNER JOIN the aggregate result as a derived table
  SELECT
    user_id,
    COUNT(*)           AS order_count, -- order count
    SUM(amount)         AS total_amount,-- total amount
    ROUND(AVG(amount))  AS avg_amount   -- average amount (rounded to integer)
  FROM   orders
  WHERE  status = 'completed'          -- narrow to completed before aggregating
  GROUP BY user_id
) AS s ON u.user_id = s.user_id      -- the AS alias is required; specify the join key with ON
ORDER BY s.total_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the derived table (s)
  2. FROM users u                  → read all 5 rows of users
  3. INNER JOIN s ON user_id       → user5 has no row in s, so it is auto-excluded from the join
  4. SELECT u.*, s.*               → select the needed columns
  5. ORDER BY s.total_amount DESC  → total amount descending
  */

Explanation (table transitions & key points) ▼

SELECT
  u.user_id, u.name, u.plan,
  s.order_count, s.total_amount, s.avg_amount
FROM   users u
INNER JOIN (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount)) AS avg_amount
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
) AS s ON u.user_id = s.user_id
ORDER BY s.total_amount DESC;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① Generate the derived table (s) ▼

SELECT user_id, COUNT(*), SUM(amount), ROUND(AVG(amount)) FROM orders WHERE status='completed' GROUP BY user_idThe inner subquery is evaluated first, generating in memory a "virtual table (s)" holding the per-user aggregate result. At this point, user5 with no orders is not included.

1 / 3

user_id	order_count	total_amount	avg_amount
1	3	35,000	11,667
2	1	3,500	3,500
3	2	20,500	10,250
4	1	2,000	2,000

Derived table (s): 4 rows generated

LEARNING POINTS

The structure of derived table × JOIN: a FROM-clause subquery (derived table) can be the target of INNER JOIN / LEFT JOIN just like an ordinary table. This pattern of combining an aggregate result with master information in a single query is very frequent in building API responses for web apps.

The implicit exclusion of INNER JOIN: INNER JOIN returns only rows whose join keys match in both tables. Here, user5 has no row on the derived-table side, so it is automatically excluded without a WHERE clause. The requirement "exclude users with no orders" is naturally achieved with INNER JOIN.

The AS alias for a derived table is required: always give a FROM-clause subquery an alias such as AS s. Omitting the alias is an error in both PostgreSQL and MySQL. In the outer query, reference columns via the alias, as in s.order_count.

Aggregate in the derived table, master info via JOIN: this pattern cleanly separates "aggregation logic" from "attaching display data." Controlling the aggregation condition in the inner WHERE and obtaining display fields like name and plan via the outer JOIN — this separation of responsibilities produces readable queries.

ANTI-PATTERNS

Forgetting the AS alias: not attaching AS s to a FROM-clause subquery is a syntax error (required in both PostgreSQL and MySQL). Column names referenced from the outside may also raise an ambiguity error unless referenced via the alias.

Confusing INNER JOIN and LEFT JOIN: if you want to "output all users and show NULL when there are no orders," use LEFT JOIN, not INNER JOIN. Choose based on the requirement. INNER JOIN is "only rows present in both"; LEFT JOIN is "all rows of the left table plus matching rows from the right table (NULL if none)."

Field note: rewriting this pattern as a CTE improves readability even further

The derived table × JOIN becomes even more readable with a CTE (Common Table Expression). Writing

WITH order_summary AS (SELECT user_id, COUNT(*) AS order_count, ... FROM orders WHERE status='completed' GROUP BY user_id) SELECT u.*, s.* FROM users u INNER JOIN order_summary s ON u.user_id = s.user_id ORDER BY s.total_amount DESC;

lets you split the aggregation logic out of the main body. CTEs are especially effective for complex queries that need multiple derived tables, making debugging and review far easier.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Multi-level nested SQ — get the product list of the "best-selling category" via nested subqueries

Multi-level SQWHERE INCategory aggregationRanking extraction

Background ▼

A subquery can contain yet another subquery (multi-level nesting). By narrowing step by step from the outside inward — "extract the category's maximum sales → identify that category name → extract that category's products" — you can express complex conditions.

SELECT * FROM products
WHERE category = (
  SELECT category          -- ② find the category name
  FROM   sales_summary
  WHERE  total = (
    SELECT MAX(total)      -- ① fix the maximum value first
    FROM   sales_summary
  )
);

Evaluation order of multi-level nesting: evaluation starts from the innermost subquery. The flow is "find the maximum value → extract the category holding that maximum → extract that category's products." It is easier to understand if you design by working backward, inner to outer.

Problem ▼

Using the following 3 tables, retrieve the product list of the category with the largest total sold quantity (qty) among completed orders (completed). Return product_id, name, category, price ordered by price descending.

Tables used

▸ products

product_id	name	category	price
1	Wireless Earbuds	electronics	8,000
2	Smartwatch	electronics	25,000
3	Cotton T-shirt	apparel	3,500
4	Denim Jacket	apparel	12,000
5	Protein Powder	health	5,000

▸ orders (relevant columns only)

order_id	status
101	completed
102	completed
103	completed
104	pending

▸ order_items

item_id	order_id	product_id	qty
1	101	1	2
2	101	3	1
3	102	2	1
4	102	1	3
5	103	4	2
6	103	5	1
7	104	2	2

Expected Output ▼

Expected output (products of the best-selling category = electronics, ordered by price descending):

product_id	name	category	price
2	Smartwatch	electronics	25,000
1	Wireless Earbuds	electronics	8,000

※ Aggregated over completed orders only. electronics: qty total = 2+3+1 = 6, apparel: 1+2 = 3, health: 1. electronics is the largest. order_id=104 is pending and is excluded.

Model Answer ▼

SELECT
  product_id, name, category, price
FROM   products
WHERE  category = (              -- extract products matching the best-selling category name
  SELECT p2.category            -- ② get the category name (scalar SQ)
  FROM   products p2
  INNER JOIN order_items oi ON p2.product_id = oi.product_id
  INNER JOIN orders o       ON oi.order_id   = o.order_id
  WHERE  o.status = 'completed'  -- completed orders only
  GROUP BY p2.category
  HAVING SUM(oi.qty) = (       -- per-category total qty equals the maximum value
    SELECT MAX(cat_qty)          -- ① fix the maximum total qty first (innermost SQ)
    FROM (
      SELECT SUM(oi2.qty) AS cat_qty -- per-category total qty
      FROM   products p3
      INNER JOIN order_items oi2 ON p3.product_id = oi2.product_id
      INNER JOIN orders o2      ON oi2.order_id  = o2.order_id
      WHERE  o2.status = 'completed'
      GROUP BY p3.category
    ) AS cat_totals              -- always give the derived table an alias
  )
)
ORDER BY price DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the innermost SQ (derived table cat_totals)
  2. 2nd-level SQ: MAX(cat_qty) = 6 (fix the maximum value)
  3. Middle SQ: get the category where HAVING SUM(oi.qty) = 6
  4. Outer WHERE: narrow to products.category = 'electronics'
  5. SELECT ...           → select the needed columns
  6. ORDER BY price DESC  → price descending
  */

Explanation (table transitions & key points) ▼

SELECT
  product_id, name, category, price
FROM   products
WHERE  category = (
  SELECT p2.category
  FROM   products p2
  INNER JOIN order_items oi ON p2.product_id = oi.product_id
  INNER JOIN orders o       ON oi.order_id   = o.order_id
  WHERE  o.status = 'completed'
  GROUP BY p2.category
  HAVING SUM(oi.qty) = (
    SELECT MAX(cat_qty)
    FROM (
      SELECT SUM(oi2.qty) AS cat_qty
      FROM   products p3
      INNER JOIN order_items oi2 ON p3.product_id = oi2.product_id
      INNER JOIN orders o2      ON oi2.order_id  = o2.order_id
      WHERE  o2.status = 'completed'
      GROUP BY p3.category
    ) AS cat_totals
  )
)
ORDER BY price DESC;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

① Innermost SQ (fetch and join data) ▼

FROM products p3 INNER JOIN order_items oi2 ... WHERE o2.status='completed'Evaluation begins from the deepest subquery. First, join the completed order line items (order_items) with product info (products) to prepare the base data for aggregation.

1 / 6

o2.order_id	category	qty	status
101	electronics	2	completed
101	apparel	1	completed
102	electronics	1	completed
102	electronics	3	completed
103	apparel	2	completed
103	health	1	completed
104	electronics	2	pending (excluded)

Target line items: 6 rows

LEARNING POINTS

Multi-level nesting is evaluated "inner to outer": the innermost SELECT MAX(cat_qty) FROM (...) is evaluated first and returns a constant value (6). Next, the middle SQ gets the category name with HAVING SUM(oi.qty) = 6, and finally the outer query extracts that category's products. When designing, the trick is to work backward from the inside, asking "what should I fix in the inner query so the outer becomes simple."

Chaining scalar SQs: this problem narrows in 3 stages: "maximum qty (constant) → best category name (string) → product list." Since each stage functions as a scalar SQ returning a single value, it is important to design each stage to return exactly 1 row and 1 column.

Combining JOIN and SQ: for aggregations that require joining multiple tables, as here, the standard pattern is to put an INNER JOIN inside the subquery. By JOINing the 3 tables products, order_items, and orders inside and aggregating, and keeping the outer as a simple WHERE against products, you maintain overall readability.

A field pattern — ranking queries: ranking queries such as "the best-selling category," "the product with the most purchases this month," or "the page with the most visits" are frequent in analytics dashboards and recommendation engines. Multi-level nested SQ hurts readability, so in practice, refactoring to define each step by name with a CTE is recommended.

ANTI-PATTERNS

Repeating the same aggregation in multiple places: in this example, the "per-category qty total" is computed twice (for the maximum value and for the comparison). In production, using a CTE to reuse a single aggregation result reduces the number of evaluations. Tidy it up as in WITH cat_totals AS (...) SELECT MAX(cat_qty) FROM cat_totals.

A scalar SQ error when the same qty exists in multiple categories: if two categories happen to have the same maximum qty, the middle SQ returns 2 rows and causes a scalar SQ error. In production, you need a design that explicitly tie-breaks with LIMIT 1 or ORDER BY ... LIMIT 1, or switches to IN to handle multiple matches.

Field note: rewriting a multi-level nested SQ as a CTE makes it far more readable

Multi-level nesting is challenging to follow. In practice, splitting each step out by name with a CTE (WITH clause) lets you write the same logic far more readably. Decompose it in stages: ① WITH cat_totals AS (...per-category qty aggregation...) ② top_category AS (SELECT category FROM cat_totals WHERE cat_qty = (SELECT MAX(cat_qty) FROM cat_totals)) ③ SELECT * FROM products WHERE category IN (SELECT category FROM top_category) — this also makes debugging each step easier.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.