Subqueries in WHERE — Filtering With Inner Queries

-- Find orders above the average order amount
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount) FROM orders
);

-- The inner query returns one number: the average
-- The outer query uses that number to filter

-- Find the customer who placed the most recent order
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id = (
  SELECT customer_id
  FROM orders
  ORDER BY order_date DESC
  LIMIT 1
);

-- Find customers who have placed at least one order over 500
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total_amount > 500
);

-- Find products that have NEVER been ordered
SELECT product_id, product_name, price
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id
  FROM order_items
);

-- Find customers who have placed at least one order (EXISTS)
SELECT c.customer_id, c.first_name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- Find customers who have NEVER ordered (NOT EXISTS)
SELECT c.customer_id, c.first_name, c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- Find customers whose total spending exceeds 1000
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE (
  SELECT SUM(o.total_amount)
  FROM orders o
  WHERE o.customer_id = c.customer_id
) > 1000;

-- Find orders where the amount is above that customer's average
SELECT o.order_id, o.customer_id, o.total_amount
FROM orders o
WHERE o.total_amount > (
  SELECT AVG(o2.total_amount)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);