Nested Queries

What is a Subquery?

A subquery is a SELECT statement nested inside another SQL statement. The inner query runs first and its result is used by the outer query. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.

SQL — Subquery in WHERE
— Find customers who spend above average
SELECT customer_id, SUM(order_amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > (
  SELECT AVG(total_spend)
  FROM (
    SELECT customer_id, SUM(order_amount) AS total_spend
    FROM orders GROUP BY customer_id
  ) sub
);
Note: Deeply nested subqueries like this are hard to read. This is why CTEs were invented — they let you name each step and write it separately.
Better Approach

CTEs — Common Table Expressions

CTEs use the WITH keyword to define named temporary result sets. They make complex queries dramatically more readable. Interviewers strongly prefer candidates who use CTEs over nested subqueries.

SQL — CTE with WITH
— Same logic as above, but clean and readable
WITH customer_totals AS (
  SELECT customer_id, SUM(order_amount) AS total_spend
  FROM orders
  GROUP BY customer_id
),
avg_spend AS (
  SELECT AVG(total_spend) AS avg_total
  FROM customer_totals
)
SELECT ct.customer_id, ct.total_spend
FROM customer_totals ct, avg_spend a
WHERE ct.total_spend > a.avg_total
ORDER BY ct.total_spend DESC;
CTE benefits: Each step is named and readable. You can reference a CTE multiple times in the same query (unlike subqueries). Interviewers view CTEs as a sign of senior SQL thinking.
Advanced

Multiple CTEs — Chain Your Logic

SQL — Multiple CTEs Chained
— Step 1: get revenue per customer per region
WITH regional_spend AS (
  SELECT c.city, o.customer_id,
    SUM(o.order_amount) AS total
  FROM orders o JOIN customers c ON o.customer_id = c.id
  GROUP BY c.city, o.customer_id
),
— Step 2: rank within each city
ranked AS (
  SELECT *,
    RANK() OVER(PARTITION BY city ORDER BY total DESC) AS rnk
  FROM regional_spend
)
— Step 3: get top customer per city
SELECT city, customer_id, total
FROM ranked WHERE rnk = 1;
Pattern to memorise: CTE step 1 → transform raw data. CTE step 2 → rank or filter. Final SELECT → pick what you need. This 3-step CTE pattern solves 80% of hard interview questions.
Interview Questions

Practice Questions — Subqueries & CTEs

Easy
Find all products priced above the average product price.
InfosysWipro
+
Use a subquery in WHERE to compare each row’s price against the overall average.
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;
Medium
Using a CTE, find the top customer by revenue in each city.
PaytmRazorpay
+
CTE 1 aggregates revenue per customer+city. CTE 2 ranks within city. Final query filters rank = 1.
WITH rev AS (
  SELECT c.city, o.customer_id, SUM(o.order_amount) total
  FROM orders o JOIN customers c ON o.customer_id=c.id
  GROUP BY c.city, o.customer_id
), rnk AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY city ORDER BY total DESC) rn
  FROM rev
)
SELECT * FROM rnk WHERE rn=1;
Hard
Find customers whose order value in Q1 2024 was more than double their order value in Q4 2023.
GoogleAmazon
+
Two CTEs — one for each time period — then join and filter on the ratio condition.
WITH q1 AS (
  SELECT customer_id, SUM(order_amount) q1_rev
  FROM orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-03-31’
  GROUP BY customer_id
), q4 AS (
  SELECT customer_id, SUM(order_amount) q4_rev
  FROM orders WHERE order_date BETWEEN ‘2023-10-01’ AND ‘2023-12-31’
  GROUP BY customer_id
)
SELECT q1.customer_id, q1.q1_rev, q4.q4_rev
FROM q1 JOIN q4 ON q1.customer_id=q4.customer_id
WHERE q1.q1_rev > q4.q4_rev * 2;

Master CTEs with expert help

Book a free mock SQL session — we’ll cover real CTE questions from Google, Amazon and top startups.

Book Free Session
← PreviousSQL JOINsNext →Window Functions