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.