6-5 연습 문제 — 종합 진단 ★,
5 패턴이 한 SQL에 다 섞여 나옵니다

-- ✅ 수정 (다중 함정 모두 해결)
WITH active_members AS (
  -- 패턴 5 해결: "활성" 을 회사 정의로 명시
  SELECT DISTINCT
    m.member_id,
    m.member_name
  FROM
    members m
  JOIN orders o
    ON m.member_id = o.member_id
  WHERE
    o.ordered_at >= CURDATE() - INTERVAL 1 YEAR
),
order_totals AS (
  -- 패턴 1 해결: 객단가 = 주문별 총액의 평균. CTE 단계 분리
  SELECT
    o.order_id,
    o.member_id,
    p.category_id,
    SUM(p.price * oi.quantity) AS order_total
  FROM
    orders o
  JOIN order_items oi USING(order_id)
  JOIN products p USING(product_id)
  WHERE
    o.ordered_at >= CURDATE() - INTERVAL 1 YEAR
  GROUP BY
    o.order_id,
    o.member_id,
    p.category_id
)
SELECT
  am.member_name,
  c.category_name,
  COALESCE(AVG(ot.order_total), 0) AS 평균_객단가  -- 패턴 2 해결: NULL → 0
FROM
  active_members am
JOIN order_totals ot
  ON am.member_id = ot.member_id
JOIN categories c
  ON ot.category_id = c.category_id
GROUP BY
  am.member_id,
  am.member_name,
  c.category_id,
  c.category_name  -- 패턴 3 해결: 모든 비집계 컬럼
ORDER BY
  am.member_name,
  평균_객단가 DESC;

-- 1차 시도 (함정 포함)
SELECT
  c.category_name,
  SUM(p.price * oi.quantity) AS 매출,
  SUM(p.price * oi.quantity) / SUM(p.price * oi.quantity) * 100 AS 비중_퍼센트  -- 분모 오류
FROM
  members m
JOIN orders o
  ON m.member_id = o.member_id
JOIN order_items oi
  ON o.order_id = oi.order_id
JOIN products p
  ON oi.product_id = p.product_id
JOIN categories c
  ON p.category_id = c.category_id
WHERE
  m.gender = 'F'
  AND m.age >= 30  -- "30대" 모호
  AND o.ordered_at LIKE '2026-01%'  -- Q1 의도 - 1월만 잡힘
GROUP BY
  c.category_id;  -- category_name 누락

-- ✅ 최종 수정
SELECT
  c.category_id,
  c.category_name,
  SUM(p.price * oi.quantity) AS 매출,
  SUM(p.price * oi.quantity) * 100.0
    / (
      SELECT
        SUM(p2.price * oi2.quantity)
      FROM
        members m2
      JOIN orders o2
        ON m2.member_id = o2.member_id
      JOIN order_items oi2
        ON o2.order_id = oi2.order_id
      JOIN products p2
        ON oi2.product_id = p2.product_id
      WHERE
        m2.gender = 'F'
        AND m2.age BETWEEN 30 AND 39
        AND o2.ordered_at BETWEEN '2026-01-01' AND '2026-03-31'
    ) AS 비중_퍼센트
FROM
  members m
JOIN orders o
  ON m.member_id = o.member_id
JOIN order_items oi
  ON o.order_id = oi.order_id
JOIN products p
  ON oi.product_id = p.product_id
JOIN categories c
  ON p.category_id = c.category_id
WHERE
  m.gender = 'F'
  AND m.age BETWEEN 30 AND 39                       -- 30대 정확화
  AND o.ordered_at BETWEEN '2026-01-01' AND '2026-03-31'  -- Q1 정확화
GROUP BY
  c.category_id,
  c.category_name             -- 모든 비집계 컬럼
ORDER BY
  매출 DESC;