Mastering Subqueries

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 10;

SELECT name, price, category_id
FROM products
WHERE price = (SELECT MAX(price) FROM products);

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY name
LIMIT 15;

SELECT name, grade
FROM customers
WHERE id NOT IN (SELECT customer_id FROM reviews)
ORDER BY
    CASE grade
        WHEN 'VIP' THEN 1
        WHEN 'GOLD' THEN 2
        WHEN 'SILVER' THEN 3
        ELSE 4
    END,
    name
LIMIT 15;

SELECT name, price
FROM products
WHERE id IN (SELECT product_id FROM wishlists)
ORDER BY price DESC
LIMIT 10;

SELECT name, stock_qty
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items)
ORDER BY stock_qty DESC;

SELECT order_number, total_amount, ordered_at
FROM orders
WHERE status NOT IN ('cancelled')
  AND total_amount > (
      SELECT AVG(total_amount)
      FROM orders
      WHERE status NOT IN ('cancelled')
  )
ORDER BY total_amount DESC
LIMIT 10;

SELECT p.name, p.price, ca.avg_price
FROM products AS p
INNER JOIN (
    SELECT category_id, ROUND(AVG(price), 2) AS avg_price
    FROM products
    GROUP BY category_id
) AS ca ON p.category_id = ca.category_id
WHERE p.price > ca.avg_price
ORDER BY p.price DESC
LIMIT 15;

SELECT
    c.name,
    c.grade,
    (SELECT COUNT(*)
     FROM orders
     WHERE customer_id = c.id
       AND status NOT IN ('cancelled')) AS order_count
FROM customers AS c
WHERE (SELECT COUNT(*)
       FROM orders
       WHERE customer_id = c.id
         AND status NOT IN ('cancelled')) >= 3
ORDER BY order_count DESC
LIMIT 15;

SELECT
    p.name,
    p.price,
    (SELECT MAX(created_at)
     FROM reviews
     WHERE product_id = p.id) AS last_review_at
FROM products AS p
ORDER BY last_review_at DESC NULLS LAST
LIMIT 15;

SELECT c.name, c.grade, os.total_spent
FROM customers AS c
INNER JOIN (
    SELECT customer_id, ROUND(SUM(total_amount), 2) AS total_spent
    FROM orders
    WHERE status NOT IN ('cancelled')
    GROUP BY customer_id
) AS os ON c.id = os.customer_id
ORDER BY os.total_spent DESC
LIMIT 10;

SELECT p.name, p.price
FROM products AS p
WHERE EXISTS (
    SELECT 1
    FROM reviews
    WHERE product_id = p.id
)
ORDER BY p.name
LIMIT 15;

SELECT
    c.name AS customer,
    p.name AS product,
    w.created_at AS wishlisted_at
FROM wishlists AS w
INNER JOIN customers AS c ON w.customer_id = c.id
INNER JOIN products AS p ON w.product_id = p.id
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.customer_id = w.customer_id
      AND oi.product_id = w.product_id
      AND o.status NOT IN ('cancelled')
)
ORDER BY w.created_at DESC
LIMIT 20;

SELECT p.name, p.price, p.category_id
FROM products AS p
WHERE p.price = (
    SELECT MAX(price)
    FROM products
    WHERE category_id = p.category_id
)
ORDER BY p.price DESC;

SELECT DISTINCT p.name, p.price
FROM products AS p
WHERE p.id IN (
    SELECT oi.product_id
    FROM order_items AS oi
    WHERE oi.order_id IN (
        SELECT o.id
        FROM orders AS o
        WHERE o.status NOT IN ('cancelled')
          AND o.customer_id IN (
              SELECT id FROM customers WHERE grade = 'VIP'
          )
    )
)
AND p.id IN (
    SELECT product_id
    FROM reviews
    GROUP BY product_id
    HAVING AVG(rating) >= 4.0
)
ORDER BY p.price DESC
LIMIT 15;

SELECT
    c.name,
    c.grade,
    ca.avg_amount,
    ca.order_count
FROM customers AS c
INNER JOIN (
    SELECT
        customer_id,
        ROUND(AVG(total_amount), 2) AS avg_amount,
        COUNT(*) AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled')
    GROUP BY customer_id
) AS ca ON c.id = ca.customer_id
WHERE ca.avg_amount > (
    SELECT AVG(total_amount)
    FROM orders
    WHERE status NOT IN ('cancelled')
)
ORDER BY ca.avg_amount DESC
LIMIT 15;

SELECT
    p.name,
    ps.total_qty
FROM products AS p
INNER JOIN (
    SELECT product_id, SUM(quantity) AS total_qty
    FROM order_items
    GROUP BY product_id
) AS ps ON p.id = ps.product_id
WHERE ps.total_qty > (
    SELECT AVG(total_qty)
    FROM (
        SELECT SUM(quantity) AS total_qty
        FROM order_items
        GROUP BY product_id
    )
)
ORDER BY ps.total_qty DESC
LIMIT 15;

SELECT
    r.id,
    p.name AS product,
    r.rating,
    ROUND((SELECT AVG(rating) FROM reviews WHERE product_id = r.product_id), 2) AS avg_rating,
    r.title,
    r.created_at
FROM reviews AS r
INNER JOIN products AS p ON r.product_id = p.id
WHERE r.rating < (
    SELECT AVG(rating)
    FROM reviews
    WHERE product_id = r.product_id
)
ORDER BY r.created_at DESC
LIMIT 15;

-- (A) IN 서브쿼리 방식
SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM reviews)
ORDER BY name
LIMIT 10;

-- (B) JOIN 방식 (DISTINCT 필요)
SELECT DISTINCT c.name, c.email
FROM customers AS c
INNER JOIN reviews AS r ON c.id = r.customer_id
ORDER BY c.name
LIMIT 10;

SELECT c.name, c.grade, cc.cat_count
FROM customers AS c
INNER JOIN (
    SELECT
        o.customer_id,
        COUNT(DISTINCT p.category_id) AS cat_count
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id = oi.order_id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.status NOT IN ('cancelled')
    GROUP BY o.customer_id
    HAVING COUNT(DISTINCT p.category_id) >= 3
) AS cc ON c.id = cc.customer_id
ORDER BY cc.cat_count DESC, c.name
LIMIT 15;