SQL subqueries confuse many beginners not because they’re impossible, but because most tutorials explain syntax, not why you’d use them.

In real data jobs, subqueries help analysts:

• compare values
• filter intelligently
• answer layered business questions

Let’s break this down with 20 practical SQL subquery examples, each tied to a real business use case.

1. Find Customers With Above-Average Spending

Business question:
Who are our high-value customers?

SELECT customer_id, total_spend
FROM customers
WHERE total_spend > (
  SELECT AVG(total_spend) FROM customers
);

2. Products Priced Higher Than Category Average

Use case: Pricing analysis.

SELECT product_name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category = 'Electronics'
);

3. Employees Earning More Than Their Team Average

Use case: Compensation review.

SELECT employee_name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

4. Customers Who Have Never Placed an Order

Use case: Marketing re-engagement.

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

5. Orders Larger Than the Average Order Size

Use case: Identify large transactions.

SELECT order_id, order_amount
FROM orders
WHERE order_amount > (
  SELECT AVG(order_amount) FROM orders
);

6. Products That Have Never Been Sold

Use case: Inventory cleanup.

SELECT product_id
FROM products
WHERE product_id NOT IN (
  SELECT product_id FROM sales
);

7. Latest Order for Each Customer

Use case: Customer activity tracking.

SELECT *
FROM orders o
WHERE order_date = (
  SELECT MAX(order_date)
  FROM orders
  WHERE customer_id = o.customer_id
);

8. Departments With Above-Average Headcount

Use case: Workforce planning.

SELECT department
FROM employees
GROUP BY department
HAVING COUNT(*) > (
  SELECT AVG(emp_count)
  FROM (
    SELECT COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
  ) sub
);

9. Customers With Orders in the Last 30 Days

Use case: Active user analysis.

SELECT customer_id
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

10. Products With Sales Below Average

Use case: Performance review.

SELECT product_id, sales
FROM products
WHERE sales < (
  SELECT AVG(sales) FROM products
);

11. Employees Who Earn More Than Their Manager

Use case: Org structure checks.

SELECT e.employee_name
FROM employees e
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE employee_id = e.manager_id
);

12. Customers Who Bought More Than One Product

Use case: Cross-sell analysis.

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) > 1;

(Subquery alternative often used in reporting tools.)

13. Orders From Top 10% Customers

Use case: Revenue concentration.

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE total_spend > (
    SELECT PERCENTILE_CONT(0.9)
    WITHIN GROUP (ORDER BY total_spend)
    FROM customers
  )
);

14. Products With the Highest Price

Use case: Price benchmarking.

SELECT product_name
FROM products
WHERE price = (
  SELECT MAX(price) FROM products
);

15. Customers With No Activity This Year

Use case: Churn analysis.

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
  WHERE EXTRACT(YEAR FROM order_date) = 2026
);

16. Regions With Sales Above Company Average

Use case: Regional performance.

SELECT region
FROM sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(total_sales)
  FROM (
    SELECT SUM(amount) AS total_sales
    FROM sales
    GROUP BY region
  ) sub
);

17. Customers Who Bought the Most Expensive Product

Use case: Premium buyer identification.

SELECT customer_id
FROM orders
WHERE product_id = (
  SELECT product_id
  FROM products
  ORDER BY price DESC
  LIMIT 1
);

18. Employees With Below-Average Performance Scores

Use case: Performance review.

SELECT employee_name
FROM employees
WHERE performance_score < (
  SELECT AVG(performance_score)
  FROM employees
);

19. Products Sold More Than the Average Quantity

Use case: Demand analysis.

SELECT product_id
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > (
  SELECT AVG(total_qty)
  FROM (
    SELECT SUM(quantity) AS total_qty
    FROM sales
    GROUP BY product_id
  ) sub
);

20. Customers Who Have Placed the Largest Order

Use case: VIP identification.

SELECT customer_id
FROM orders
WHERE order_amount = (
  SELECT MAX(order_amount)
  FROM orders
);

When Should You Use Subqueries?

Use subqueries when you need to:

• compare rows to aggregated values
• filter based on another query
• answer layered business questions

In many cases, joins and CTEs can replace subqueries, but subqueries are still heavily tested in interviews.

Subqueries aren’t about memorizing syntax.

They’re about:

• thinking in steps
• breaking business questions into logic
• letting SQL do the heavy lifting

Master these, and your SQL confidence jumps fast.

FAQs