Subquery with IN

intermediate

Find products in active categories

SELECT product_id, product_name, category_id

FROM products

WHERE category_id IN (

SELECT id FROM categories WHERE active = true

)

ORDER BY product_name;

product_id | product_name | category_id

1 | Keyboard | 2

2 | Laptop | 1

3 | Mouse | 2

Subquery returns active category IDs. Main query filters products to those categories.

All

Above Average Filtering

intermediate

Find orders above average order value

SELECT order_id, customer_id, total

FROM orders

WHERE total > (SELECT AVG(total) FROM orders)

ORDER BY total DESC;

order_id | customer_id | total

102 | 2 | 500.00

101 | 1 | 350.00

Subquery calculates average. Main query filters to above-average orders. Dynamic threshold.

All

CTE for Complex Filtering

advanced

Find customers with high-value orders

WITH high_value_customers AS (

SELECT customer_id, SUM(total) AS total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(total) > 5000

)

SELECT c.customer_id, c.name, h.total_spent

FROM customers c

JOIN high_value_customers h ON c.customer_id = h.customer_id

ORDER BY h.total_spent DESC;