#Step 07 — 조인(JOIN)

SELECT
    o.order_id,
    o.order_date,
    c.name  AS 고객명,
    c.grade AS 등급,
    o.total_amount
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id
LIMIT 5;

SELECT
    o.order_id,
    c.name        AS 고객,
    p.name        AS 상품,
    cat.name      AS 카테고리,
    oi.quantity   AS 수량,
    oi.unit_price AS 단가
FROM orders o
JOIN customers c    ON c.customer_id   = o.customer_id
JOIN order_items oi ON oi.order_id     = o.order_id
JOIN products p     ON p.product_id    = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
ORDER BY o.order_id, p.product_id
LIMIT 8;

SELECT
    cat.category_id,
    cat.name      AS 카테고리,
    p.product_id,
    p.name        AS 상품명
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.category_id
WHERE cat.parent_id IS NULL     -- 대분류만
ORDER BY cat.category_id;

SELECT
    cat.category_id,
    cat.name            AS 카테고리,
    COUNT(p.product_id) AS 상품수,
    COUNT(*)            AS `COUNT(*)_함정`
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.category_id
GROUP BY cat.category_id, cat.name
ORDER BY 상품수, cat.category_id
LIMIT 8;

-- (A) 조건을 ON 에: 조인 짝을 만들 때만 적용. 왼쪽(고객)은 전부 보존된다.
SELECT COUNT(*) AS 조건이_ON
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'DELIVERED';

-- (B) 조건을 WHERE 에: 조인 "후" 적용. NULL 확장된 행이 탈락 → 사실상 INNER JOIN.
SELECT COUNT(*) AS 조건이_WHERE
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
WHERE o.status = 'DELIVERED';

SELECT c.customer_id, c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'DELIVERED'
WHERE c.customer_id = 6;

SELECT o.order_id, o.status, o.total_amount
FROM orders o
LEFT JOIN payments pay ON pay.order_id = o.order_id
WHERE pay.payment_id IS NULL      -- 짝이 없었던 행만
ORDER BY o.order_id
LIMIT 8;

SELECT
    (SELECT COUNT(*) FROM orders o
       LEFT JOIN payments pay ON pay.order_id = o.order_id
      WHERE pay.payment_id IS NULL)  AS 안티조인_결과,
    (SELECT COUNT(*) FROM orders WHERE status = 'PENDING') AS PENDING_주문수;

-- 후기를 한 번도 안 남긴 고객 (Step 05 의 NOT EXISTS 를 조인 버전으로)
SELECT c.customer_id, c.name, c.grade
FROM customers c
LEFT JOIN reviews r ON r.customer_id = c.customer_id
WHERE r.review_id IS NULL
ORDER BY c.customer_id
LIMIT 8;

SELECT
    e.employee_id,
    e.name     AS 사원,
    e.position AS 직급,
    m.name     AS 관리자,
    m.position AS 관리자_직급
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id
LIMIT 10;

SELECT
    e.name   AS 사원,
    e.dept   AS 부서,
    e.salary AS 급여,
    COUNT(h.employee_id) AS 나보다_높은_사람수
FROM employees e
LEFT JOIN employees h
       ON h.dept = e.dept
      AND h.salary > e.salary
GROUP BY e.employee_id, e.name, e.dept, e.salary
ORDER BY e.dept, e.salary DESC
LIMIT 10;

SELECT g.grade, ct.city
FROM (SELECT DISTINCT grade FROM customers) g
CROSS JOIN (SELECT DISTINCT city FROM customers WHERE city IN ('서울','부산','인천')) ct
ORDER BY g.grade, ct.city;

SELECT
    g.grade,
    ct.city,
    COUNT(c.customer_id) AS 고객수
FROM (SELECT DISTINCT grade FROM customers) g
CROSS JOIN (SELECT DISTINCT city FROM customers) ct
LEFT JOIN customers c ON c.grade = g.grade AND c.city = ct.city
GROUP BY g.grade, ct.city
HAVING COUNT(c.customer_id) > 0
ORDER BY g.grade, 고객수 DESC
LIMIT 10;

SELECT customer_id, o.order_id, o.total_amount
FROM orders o
JOIN customers c USING (customer_id)
ORDER BY o.order_id
LIMIT 5;

SELECT
    p.product_id,
    p.name,
    COUNT(oi.order_item_id) AS 판매_횟수
FROM order_items oi
RIGHT JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY 판매_횟수 ASC, p.product_id
LIMIT 5;

SELECT * FROM a FULL OUTER JOIN b ON ...;    -- ERROR 1064 (문법 에러)

WITH
  q2024 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'),
  q2025 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01')
SELECT a.customer_id AS `2024_1분기`, b.customer_id AS `2025_1분기`
FROM q2024 a
LEFT JOIN q2025 b ON b.customer_id = a.customer_id
UNION
SELECT a.customer_id, b.customer_id
FROM q2024 a
RIGHT JOIN q2025 b ON b.customer_id = a.customer_id
ORDER BY `2024_1분기`, `2025_1분기`
LIMIT 6;

WITH
  q2024 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'),
  q2025 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01')
SELECT a.customer_id AS `2024만`, b.customer_id AS `2025만`
FROM q2024 a LEFT JOIN q2025 b ON b.customer_id = a.customer_id
WHERE b.customer_id IS NULL
UNION
SELECT a.customer_id, b.customer_id
FROM q2024 a RIGHT JOIN q2025 b ON b.customer_id = a.customer_id
WHERE a.customer_id IS NULL;

SELECT
    c.customer_id,
    c.name                          AS 고객,
    c.grade                         AS 등급,
    COUNT(DISTINCT o.order_id)      AS 주문수,
    COUNT(DISTINCT pay.payment_id)  AS 결제수,
    COUNT(DISTINCT r.review_id)     AS 후기수
FROM customers c
LEFT JOIN orders   o   ON o.customer_id = c.customer_id
LEFT JOIN payments pay ON pay.order_id  = o.order_id
LEFT JOIN reviews  r   ON r.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.grade
ORDER BY 후기수 DESC, 주문수 DESC
LIMIT 8;

-- =====================================================================
-- Step 07 — 조인(JOIN) : practice.sql
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop -t < practice.sql
-- =====================================================================
USE shop;

-- [7-1] INNER JOIN — 양쪽에 다 있는 것만
SELECT
    o.order_id,
    o.order_date,
    c.name       AS 고객명,
    c.grade      AS 등급,
    o.total_amount
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id
LIMIT 5;

-- [7-1] INNER 는 생략 가능 (JOIN = INNER JOIN)
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LIMIT 3;

-- [7-2] 다중 조인 — 주문 → 상세 → 상품 → 카테고리
SELECT
    o.order_id,
    c.name        AS 고객,
    p.name        AS 상품,
    cat.name      AS 카테고리,
    oi.quantity   AS 수량,
    oi.unit_price AS 단가
FROM orders o
JOIN customers c    ON c.customer_id  = o.customer_id
JOIN order_items oi ON oi.order_id    = o.order_id
JOIN products p     ON p.product_id   = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
ORDER BY o.order_id, p.product_id
LIMIT 8;

-- [7-3] LEFT JOIN — 왼쪽은 전부 남기고, 오른쪽은 없으면 NULL
--   categories 5개 대분류는 직접 매달린 상품이 없다 → 상품 컬럼이 NULL
SELECT
    cat.category_id,
    cat.name       AS 카테고리,
    p.product_id,
    p.name         AS 상품명
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.category_id
WHERE cat.parent_id IS NULL
ORDER BY cat.category_id;

-- [7-3] LEFT JOIN + COUNT — 상품이 0개인 카테고리도 보인다
--   ⚠️ COUNT(*) 가 아니라 COUNT(오른쪽컬럼) 을 써야 0 이 나온다!
SELECT
    cat.category_id,
    cat.name              AS 카테고리,
    COUNT(p.product_id)   AS 상품수,
    COUNT(*)              AS `COUNT(*)_함정`
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.category_id
GROUP BY cat.category_id, cat.name
ORDER BY 상품수, cat.category_id
LIMIT 8;

-- [7-4] ON vs WHERE — LEFT JOIN 에서 필터 위치가 결과를 바꾼다 (핵심)
-- 조건을 ON 에 두면: 조인 짝을 만들 때만 적용 → 왼쪽 행은 전부 보존
SELECT COUNT(*) AS 조건이_ON
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'DELIVERED';

-- 조건을 WHERE 에 두면: 조인 "후" 적용 → NULL 확장된 행이 탈락 → INNER JOIN 이 됨
SELECT COUNT(*) AS 조건이_WHERE
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
WHERE o.status = 'DELIVERED';

-- [7-4] 눈으로 확인 — 배송완료 주문이 하나도 없는 고객
--   (예: 주문이 전부 CANCELLED 인 강소라)
SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.status
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'DELIVERED'
WHERE c.customer_id = 6;

-- [7-5] 안티조인 ① — LEFT JOIN ... IS NULL
--   "결제가 없는 주문" = PENDING 주문 60건
SELECT
    o.order_id,
    o.status,
    o.total_amount
FROM orders o
LEFT JOIN payments pay ON pay.order_id = o.order_id
WHERE pay.payment_id IS NULL
ORDER BY o.order_id
LIMIT 8;

-- [7-5] 개수로 확인 — LEFT JOIN IS NULL vs 순수 조건
SELECT
    (SELECT COUNT(*) FROM orders o
       LEFT JOIN payments pay ON pay.order_id = o.order_id
      WHERE pay.payment_id IS NULL)     AS 안티조인_결과,
    (SELECT COUNT(*) FROM orders
      WHERE status = 'PENDING')          AS PENDING_주문수;

-- [7-5] 안티조인 ② — 후기를 한 번도 안 남긴 고객 (Step 05 의 NOT EXISTS 를 조인으로)
SELECT
    c.customer_id,
    c.name,
    c.grade
FROM customers c
LEFT JOIN reviews r ON r.customer_id = c.customer_id
WHERE r.review_id IS NULL
ORDER BY c.customer_id
LIMIT 8;

-- [7-6] SELF JOIN — 같은 테이블을 두 번 (사원 ↔ 관리자)
SELECT
    e.employee_id,
    e.name       AS 사원,
    e.position   AS 직급,
    m.name       AS 관리자,
    m.position   AS 관리자_직급
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id
LIMIT 10;

-- [7-6] SELF JOIN 으로 "나보다 월급 많은 같은 부서 사람" 세기
SELECT
    e.name       AS 사원,
    e.dept       AS 부서,
    e.salary     AS 급여,
    COUNT(h.employee_id) AS 나보다_높은_사람수
FROM employees e
LEFT JOIN employees h
       ON h.dept = e.dept
      AND h.salary > e.salary
GROUP BY e.employee_id, e.name, e.dept, e.salary
ORDER BY e.dept, e.salary DESC
LIMIT 10;

-- [7-7] CROSS JOIN — 모든 조합 (곱집합)
--   등급 4종 × 도시 3종 = 12행. 조건 없이 곱한다.
SELECT g.grade, ct.city
FROM (SELECT DISTINCT grade FROM customers) g
CROSS JOIN (SELECT DISTINCT city FROM customers WHERE city IN ('서울','부산','인천')) ct
ORDER BY g.grade, ct.city;

-- [7-7] CROSS JOIN 의 실용 예: 등급×도시 조합별 고객 수 (없는 조합도 0으로)
SELECT
    g.grade,
    ct.city,
    COUNT(c.customer_id) AS 고객수
FROM (SELECT DISTINCT grade FROM customers) g
CROSS JOIN (SELECT DISTINCT city FROM customers) ct
LEFT JOIN customers c ON c.grade = g.grade AND c.city = ct.city
GROUP BY g.grade, ct.city
HAVING COUNT(c.customer_id) > 0
ORDER BY g.grade, 고객수 DESC
LIMIT 10;

-- [7-8] USING — 조인 컬럼 이름이 양쪽 같을 때 축약
--   ON a.customer_id = b.customer_id  →  USING (customer_id)
--   USING 컬럼은 결과에 한 번만 나온다 (SELECT * 시 차이)
SELECT customer_id, o.order_id, o.total_amount
FROM orders o
JOIN customers c USING (customer_id)
ORDER BY o.order_id
LIMIT 5;

-- [7-9] RIGHT JOIN — LEFT 의 방향만 바꾼 것 (실무에선 잘 안 씀)
--   products 를 전부 남기고 order_items 를 매칭
SELECT
    p.product_id,
    p.name,
    COUNT(oi.order_item_id) AS 판매_횟수
FROM order_items oi
RIGHT JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY 판매_횟수 ASC, p.product_id
LIMIT 5;

-- [7-10] MySQL 엔 FULL OUTER JOIN 이 없다 → UNION 으로 우회
--   먼저 실패를 확인: FULL JOIN 은 문법 에러
--   SELECT * FROM a FULL OUTER JOIN b ON ...;   ← ERROR 1064
--
--   질문: "2024년 1분기 주문 고객" 과 "2025년 1분기 주문 고객" 을 비교하고 싶다.
--   한쪽에만 있는 고객까지 전부 보려면 FULL OUTER JOIN 이 필요하다.
--   우회: (LEFT JOIN) UNION (RIGHT JOIN). UNION 이 중복(양쪽 매칭 행)을 제거한다.
WITH
  q2024 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'),
  q2025 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01')
SELECT a.customer_id AS `2024_1분기`, b.customer_id AS `2025_1분기`
FROM q2024 a
LEFT JOIN q2025 b ON b.customer_id = a.customer_id
UNION
SELECT a.customer_id, b.customer_id
FROM q2024 a
RIGHT JOIN q2025 b ON b.customer_id = a.customer_id
ORDER BY `2024_1분기`, `2025_1분기`
LIMIT 6;

-- [7-10] 한쪽에만 있는 고객만 추리기 (FULL OUTER + 대칭차집합)
--   2024 1분기에만 주문(15번) / 2025 1분기에만 주문(25번)
WITH
  q2024 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'),
  q2025 AS (SELECT DISTINCT customer_id FROM orders
             WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01')
SELECT a.customer_id AS `2024만`, b.customer_id AS `2025만`
FROM q2024 a LEFT JOIN q2025 b ON b.customer_id = a.customer_id
WHERE b.customer_id IS NULL
UNION
SELECT a.customer_id, b.customer_id
FROM q2024 a RIGHT JOIN q2025 b ON b.customer_id = a.customer_id
WHERE a.customer_id IS NULL;

-- [7-11] 종합: 고객별 주문/결제/후기 요약 (LEFT JOIN 여러 개 + 집계)
--   ⚠️ 여러 1:N 을 한꺼번에 조인하면 행이 곱해진다 → COUNT(DISTINCT) 로 방어
SELECT
    c.customer_id,
    c.name                              AS 고객,
    c.grade                             AS 등급,
    COUNT(DISTINCT o.order_id)          AS 주문수,
    COUNT(DISTINCT pay.payment_id)      AS 결제수,
    COUNT(DISTINCT r.review_id)         AS 후기수,
    FORMAT(COALESCE(SUM(DISTINCT o.total_amount), 0), 0) AS 주문금액참고
FROM customers c
LEFT JOIN orders   o   ON o.customer_id = c.customer_id
LEFT JOIN payments pay ON pay.order_id  = o.order_id
LEFT JOIN reviews  r   ON r.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.grade
ORDER BY 후기수 DESC, 주문수 DESC
LIMIT 8;

-- =====================================================================
-- Step 07 — 조인(JOIN) : exercise.sql (문제)
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop -t < exercise.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [문제 1] INNER JOIN 기본
--   orders 와 customers 를 조인해서, 서울에 사는 고객의 주문 중
--   금액(total_amount) 상위 5건을 조회하세요.
--   (order_id, 고객명, total_amount)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 2] 카테고리 두 번 조인 (소분류 + 대분류)
--   products 기준으로, 각 상품의 카테고리명(소분류)과 상위 카테고리명(대분류)을
--   함께 보이세요. categories 를 두 번 조인해야 합니다.
--   (product_id, 상품명, 소분류명, 대분류명) — product_id 순, 상위 10개.
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 3] 주문 없는 고객 (안티조인)
--   주문을 한 번도 안 한 고객이 있는지 LEFT JOIN ... IS NULL 로 확인하세요.
--   몇 명인가요?
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 4] 부하가 없는 말단 사원 (SELF JOIN 안티조인)
--   employees 에서 자신의 employee_id 가 누구의 manager_id 로도 쓰이지 않는
--   사원(= 부하 직원이 없는 말단)을 찾으세요.
--   (employee_id, name) — employee_id 순.
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 5] 결제 상태별 주문 수
--   orders LEFT JOIN payments 로, 결제 상태(pay.status)별 주문 수를 세세요.
--   - DONE(정상결제) / REFUNDED(환불) / 결제 없음(NULL) 세 그룹이 나와야 합니다.
--   - NULL 그룹은 '(무결제)' 로 표시하세요. (힌트: COALESCE)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 6] 카테고리별 매출 (0도 보이게)
--   전체 카테고리(17개)를 기준으로 매출 리포트를 만드세요.
--   - 취소(CANCELLED) 주문은 매출에서 제외
--   - 매출이 0인 카테고리(상품이 없는 대분류 등)도 0으로 보이게
--   (category_id, 카테고리명, 매출) — 매출 내림차순.
--   힌트: 취소 제외는 ON 이 아니라 SUM(CASE WHEN ...) 로 해야 정확합니다. 왜일까요?
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- [문제 7] ON vs WHERE 차이 확인
--   아래 두 쿼리를 각각 실행해 COUNT 를 비교하고, 왜 다른지 주석으로 쓰세요.
--     (A) LEFT JOIN ... ON ... AND o.total_amount > 5000000
--     (B) LEFT JOIN ... ON ...  WHERE o.total_amount > 5000000
-- ---------------------------------------------------------------------
-- (A)
SELECT COUNT(*) AS A
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.total_amount > 5000000;
-- (B)
SELECT COUNT(*) AS B
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.total_amount > 5000000;
--   → 왜 다른가요? (여기에 설명)


-- ---------------------------------------------------------------------
-- [문제 8] 후기가 하나도 없는 카테고리
--   "상품은 있지만 후기(reviews)가 하나도 달리지 않은 카테고리" 를 찾으세요.
--   안티조인 또는 NOT EXISTS 로 작성하세요.
-- ---------------------------------------------------------------------

-- =====================================================================
-- Step 07 — 조인(JOIN) : solution.sql (정답 + 해설)
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop -t < solution.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [정답 1] INNER JOIN 기본
--   서울 고객 조건은 customers(왼쪽 개념) 조건이므로 WHERE 에 둡니다.
--   결과: 김민수의 4,380,000원 주문 5건이 상위를 차지합니다
--        (동점이라 order_id 로 tie-break 하면 더 안정적).
-- ---------------------------------------------------------------------
SELECT o.order_id, c.name AS 고객명, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.city = '서울'
ORDER BY o.total_amount DESC, o.order_id
LIMIT 5;

-- ---------------------------------------------------------------------
-- [정답 2] 카테고리 두 번 조인 (소분류 + 대분류)
--   sub  = 상품이 직접 속한 소분류 (products.category_id)
--   main = 그 소분류의 상위 (sub.parent_id)
--   같은 테이블(categories)을 서로 다른 별칭으로 두 번 조인합니다.
-- ---------------------------------------------------------------------
SELECT
    p.product_id,
    p.name       AS 상품명,
    sub.name     AS 소분류,
    main.name    AS 대분류
FROM products p
JOIN categories sub  ON sub.category_id  = p.category_id
JOIN categories main ON main.category_id = sub.parent_id
ORDER BY p.product_id
LIMIT 10;

-- ---------------------------------------------------------------------
-- [정답 3] 주문 없는 고객 (안티조인)
--   결과: 0명. 30명 모두 주문이 있습니다(시드가 전원에게 20건씩 배정).
--   "0명"을 확인하는 것도 훌륭한 데이터 검증입니다.
-- ---------------------------------------------------------------------
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- ---------------------------------------------------------------------
-- [정답 4] 부하가 없는 말단 사원 (SELF JOIN 안티조인)
--   e 를 기준으로, e.employee_id 를 manager_id 로 가진 부하(s)를 LEFT JOIN.
--   부하가 없으면 s 가 NULL 확장 → WHERE s.employee_id IS NULL 로 걸러냄.
--   결과: 9~18번 (시니어/주니어 실무자 10명). 1~8번은 누군가의 관리자.
-- ---------------------------------------------------------------------
SELECT e.employee_id, e.name
FROM employees e
LEFT JOIN employees s ON s.manager_id = e.employee_id
WHERE s.employee_id IS NULL
ORDER BY e.employee_id;

-- ---------------------------------------------------------------------
-- [정답 5] 결제 상태별 주문 수
--   PENDING 주문은 payments 에 짝이 없어 pay.status 가 NULL 이 됩니다.
--   COALESCE 로 NULL 을 '(무결제)' 로 표시. GROUP BY 는 NULL 도 한 그룹으로 묶습니다.
--   결과: DONE 480 / REFUNDED 60 / (무결제) 60
-- ---------------------------------------------------------------------
SELECT
    COALESCE(pay.status, '(무결제)')   AS 결제상태,
    COUNT(DISTINCT o.order_id)          AS 주문수
FROM orders o
LEFT JOIN payments pay ON pay.order_id = o.order_id
GROUP BY pay.status
ORDER BY 주문수 DESC;

-- ---------------------------------------------------------------------
-- [정답 6] 카테고리별 매출 (0도 보이게)
--   핵심: 취소 제외를 "ON o.status <> 'CANCELLED'" 로 하면 틀립니다!
--   그 조건은 orders 를 매칭할지만 정할 뿐, order_items(oi)의 값은 그대로
--   남아 NULL 확장된 취소 주문의 상세가 매출에 섞여 들어갑니다.
--   → SUM(CASE WHEN o.status <> 'CANCELLED' THEN ... END) 로 "합산 여부"를
--     제어해야 정확합니다.
--   결과: 노트북 327,900,000 이 1위. 상품 없는 대분류 5개는 매출 0.
-- ---------------------------------------------------------------------
SELECT
    cat.category_id,
    cat.name AS 카테고리,
    FORMAT(
      COALESCE(SUM(CASE WHEN o.status <> 'CANCELLED'
                        THEN oi.quantity * oi.unit_price END), 0), 0
    ) AS 매출
FROM categories cat
LEFT JOIN products    p  ON p.category_id = cat.category_id
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders      o  ON o.order_id    = oi.order_id
GROUP BY cat.category_id, cat.name
ORDER BY SUM(CASE WHEN o.status <> 'CANCELLED'
                  THEN oi.quantity * oi.unit_price END) DESC
LIMIT 12;

-- ---------------------------------------------------------------------
-- [정답 7] ON vs WHERE 차이 확인
--   (A) = 46, (B) = 20.
--   (A) 조건이 ON: 500만원 초과 주문만 매칭하되, 그런 주문이 없는 고객도
--       NULL 확장으로 남습니다. 500만 초과 주문 20건 + 그런 주문이 하나도
--       없는 고객 26명 = 46.
--   (B) 조건이 WHERE: 조인 후 필터. NULL 확장 행은 o.total_amount 가 NULL 이라
--       NULL > 5000000 = UNKNOWN 으로 탈락 → 사실상 INNER JOIN → 20.
--   교훈: LEFT JOIN 의 오른쪽 조건을 WHERE 에 두면 LEFT 가 INNER 로 퇴화한다.
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS A
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.total_amount > 5000000;

SELECT COUNT(*) AS B
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.total_amount > 5000000;

-- ---------------------------------------------------------------------
-- [정답 8] 후기가 하나도 없는 카테고리
--   "상품은 있으나 그 카테고리의 어떤 상품에도 후기가 없는" 카테고리.
--   먼저 상품이 있는 카테고리로 좁히고(EXISTS products),
--   그 카테고리 상품에 후기가 하나도 없음(NOT EXISTS reviews)을 확인합니다.
--   결과: 소설(52). 소설 카테고리의 상품(여름의 끝에서)에는 후기가 없습니다.
-- ---------------------------------------------------------------------
SELECT cat.category_id, cat.name
FROM categories cat
WHERE EXISTS (
        SELECT 1 FROM products p WHERE p.category_id = cat.category_id
      )
  AND NOT EXISTS (
        SELECT 1
        FROM products p
        JOIN reviews  r ON r.product_id = p.product_id
        WHERE p.category_id = cat.category_id
      )
ORDER BY cat.category_id;