SQL 디버깅

-- 수정: 리뷰를 서브쿼리로 분리
SELECT
    p.name,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    review.avg_rating
FROM products AS p
INNER JOIN order_items AS oi ON p.id = oi.product_id
INNER JOIN orders AS o ON oi.order_id = o.id
LEFT JOIN (
    SELECT product_id, ROUND(AVG(rating), 2) AS avg_rating
    FROM reviews GROUP BY product_id
) AS review ON p.id = review.product_id
WHERE o.ordered_at LIKE '2024%'
GROUP BY p.id, p.name, review.avg_rating;

-- 수정: IS NULL 사용
SELECT name, email
FROM customers
WHERE birth_date IS NULL;

-- 수정: 조건을 ON 절로 이동
SELECT p.name, p.price, r.rating
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id AND r.rating >= 3;

-- 수정: GROUP BY 추가
SELECT cat.name, COUNT(*) AS product_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.name;

-- 수정
SELECT cat.name, COUNT(*) AS expensive_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.price >= 100000
GROUP BY cat.name
HAVING COUNT(*) >= 5;

-- 수정: COUNT(DISTINCT ...)
SELECT
    c.name,
    COUNT(DISTINCT p.category_id) AS category_count
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
GROUP BY c.id, c.name
ORDER BY category_count DESC
LIMIT 10;

-- 수정 방법 1: 전체 평균과 비교
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 수정 방법 2: 자기 카테고리 평균과 비교 (상관 서브쿼리)
SELECT p.name, p.price
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category_id = p.category_id
);

-- 수정: 시간 끝까지 포함
SELECT COUNT(*) AS december_orders
FROM orders
WHERE ordered_at BETWEEN '2024-12-01' AND '2024-12-31 23:59:59';

-- 또는 LIKE 사용
SELECT COUNT(*) AS december_orders
FROM orders
WHERE ordered_at LIKE '2024-12%';

-- 수정: 상태 필터 추가
SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    SUM(total_amount) AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY month;

-- 수정: NULLIF로 0 나누기 방지
SELECT
    s.company_name,
    COUNT(DISTINCT ret.id) AS return_count,
    COUNT(DISTINCT oi.id) AS sale_count,
    ROUND(100.0 * COUNT(DISTINCT ret.id)
        / NULLIF(COUNT(DISTINCT oi.id), 0), 2) AS return_rate
FROM suppliers AS s
LEFT JOIN products AS p ON s.id = p.supplier_id
LEFT JOIN order_items AS oi ON p.id = oi.product_id
LEFT JOIN returns AS ret ON ret.order_id = oi.order_id
GROUP BY s.id, s.company_name;

-- 수정: 원래 표현식을 WHERE에 사용
SELECT
    CASE WHEN price < 100000 THEN '저가' ELSE '고가' END AS tier,
    COUNT(*) AS cnt
FROM products
WHERE price < 100000
GROUP BY tier;

-- 또는 HAVING 사용 (그룹 필터)
SELECT
    CASE WHEN price < 100000 THEN '저가' ELSE '고가' END AS tier,
    COUNT(*) AS cnt
FROM products
GROUP BY tier
HAVING tier = '저가';

-- 수정: 동일 구조로 맞추기
SELECT '주문' AS type, order_number AS reference, ordered_at AS event_date
FROM orders
UNION ALL
SELECT '문의' AS type, title AS reference, created_at AS event_date
FROM complaints
ORDER BY event_date DESC
LIMIT 20;