What are subqueries in SQL?

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Products above average price
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Users with no orders
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- Top spending users
SELECT u.name, totals.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) as total_spent
  FROM orders
  GROUP BY user_id
) totals ON u.id = totals.user_id
ORDER BY totals.total_spent DESC
LIMIT 10;

SELECT
  name,
  price,
  (SELECT AVG(price) FROM products) as avg_price,
  price - (SELECT AVG(price) FROM products) as diff_from_avg
FROM products;

-- Users whose orders exceed their average
SELECT * FROM orders o
WHERE total > (
  SELECT AVG(total)
  FROM orders
  WHERE user_id = o.user_id
);

-- Latest order for each user
SELECT * FROM orders o1
WHERE created_at = (
  SELECT MAX(created_at)
  FROM orders o2
  WHERE o2.user_id = o1.user_id
);

-- Users with at least one order
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);