Course contentsShow
← Data Science
Lesson 981 of 2,14525. SQL Subqueries and CTEsFree lesson

Common Use Cases and Examples

Apply correlated subqueries to solve problems like finding values above category averages or latest records.

Common Use Cases and Examples

What You'll Learn

You'll see practical, real-world scenarios where correlated subqueries solve problems that simpler queries cannot handle.

Why Correlated Subqueries Matter

Correlated subqueries shine when you need to compare each row against a context-specific calculation. Think of it like comparing each student's test score not to the class average, but to their specific grade level's average.

Classic Use Cases

1. Finding Values Above Category Averages

Suppose you want employees earning more than their department's average salary:

SELECT employee_name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

The subquery recalculates the average for each employee's specific department, not the company-wide average.

2. Getting the Most Recent Record per Group

Find each customer's latest order:

SELECT customer_id, order_date, total_amount
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

For every order, the subquery finds that specific customer's maximum date.

3. Finding Top Performers per Category

Products with above-average sales in their category:

SELECT product_name, category, sales
FROM products p1
WHERE sales > (
    SELECT AVG(sales)
    FROM products p2
    WHERE p2.category = p1.category
);

The Pattern

Notice the pattern: the outer query processes rows one-by-one, and the correlated subquery provides a personalized comparison value based on matching criteria (same department, same customer, same category).

Key Takeaway: Correlated subqueries are ideal for row-by-row comparisons against group-specific calculations like finding above-average values per category or the latest record per entity—problems that require each row to be evaluated against its own relevant subset of data.