Subqueries

-- Find customers who have placed at least one order
SELECT first_name, last_name, email
FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id FROM orders
)
ORDER BY last_name;

-- Find products that have NEVER been ordered
SELECT name, price, category
FROM products
WHERE id NOT IN (
  SELECT DISTINCT product_id FROM order_items
)
ORDER BY name;

-- Employees earning above the company average salary
SELECT first_name, last_name, salary, department
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;

-- Show each product's price alongside the overall average price
SELECT name,
       price,
       ROUND((SELECT AVG(price) FROM products), 2)  AS avg_price,
       price - ROUND((SELECT AVG(price) FROM products), 2) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

-- Show total revenue alongside each order (scalar subquery in SELECT)
SELECT id,
       total_amount,
       ROUND((SELECT SUM(total_amount) FROM orders WHERE status != 'cancelled'), 2) AS company_total,
       ROUND(total_amount / (SELECT SUM(total_amount) FROM orders WHERE status != 'cancelled') * 100, 2) AS pct_of_total
FROM orders
WHERE status = 'delivered'
ORDER BY pct_of_total DESC
LIMIT 5;

-- Aggregate orders per customer, then filter on the summary
SELECT customer_summary.customer_id,
       customer_summary.order_count,
       customer_summary.total_spent
FROM (
  SELECT customer_id,
         COUNT(*)                    AS order_count,
         ROUND(SUM(total_amount), 2) AS total_spent
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY customer_id
) AS customer_summary
WHERE customer_summary.total_spent > 300
ORDER BY customer_summary.total_spent DESC;

-- Find categories where average product price exceeds the store-wide average
SELECT category, avg_price
FROM (
  SELECT category, ROUND(AVG(price), 2) AS avg_price
  FROM products
  GROUP BY category
) AS cat_avg
WHERE avg_price > (SELECT AVG(price) FROM products)
ORDER BY avg_price DESC;

-- EXISTS: customers who have placed at least one order
SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id   -- correlated: references outer query's c.id
)
ORDER BY c.last_name;

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

-- EXISTS: products that appear in at least one order
SELECT p.name, p.price
FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.product_id = p.id
)
ORDER BY p.name;

-- For each customer, show their most recent order date (correlated)
SELECT c.first_name,
       c.last_name,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.id) AS last_order_date
FROM customers c
ORDER BY last_order_date DESC NULLS LAST
LIMIT 5;

-- For each product, show how many times it has been ordered (correlated)
SELECT p.name,
       p.price,
       (SELECT SUM(oi.quantity)
        FROM order_items oi
        WHERE oi.product_id = p.id) AS total_units_sold
FROM products p
ORDER BY total_units_sold DESC NULLS LAST
LIMIT 5;

-- Same result — subquery vs JOIN

-- Subquery approach: customers who have ordered
SELECT first_name, last_name FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

-- JOIN approach: customers who have ordered
SELECT DISTINCT c.first_name, c.last_name
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- For existence checks, NOT EXISTS is typically cleaner than NOT IN
-- NOT IN has a NULL trap: if any value in the subquery is NULL,
-- the entire NOT IN returns false for all rows

-- Safe: NOT EXISTS
SELECT name FROM products
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = products.id);

-- Risky if product_id can be NULL: NOT IN
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);  -- may miss rows if NULL exists