#Step 17 — 윈도우 함수

SELECT category_id,
       COUNT(*)   AS cnt,
       AVG(price) AS avg_price
FROM products
WHERE category_id IN (21, 22)
GROUP BY category_id;

SELECT product_id, category_id, name, price,
       AVG(price) OVER (PARTITION BY category_id) AS cat_avg_price,
       price - AVG(price) OVER (PARTITION BY category_id) AS diff_from_avg
FROM products
WHERE category_id IN (21, 22)
ORDER BY category_id, price DESC;

SELECT product_id, name, price,
       SUM(price)   OVER () AS total_price,
       COUNT(*)     OVER () AS row_cnt,
       ROUND(price / SUM(price) OVER () * 100, 1) AS pct
FROM products
WHERE category_id = 13
ORDER BY price DESC;

SELECT dept, name, salary,
       MAX(salary) OVER (PARTITION BY dept)                    AS dept_max,
       ROUND(salary / MAX(salary) OVER (PARTITION BY dept), 2) AS ratio_to_top
FROM employees
WHERE dept IN ('개발본부', '영업본부')
ORDER BY dept, salary DESC;

WITH sold AS (
  SELECT p.category_id, p.product_id, p.name, SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'CANCELLED'
  GROUP BY p.category_id, p.product_id, p.name
)
SELECT category_id, name, qty,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY qty DESC) AS rn,
       RANK()       OVER (PARTITION BY category_id ORDER BY qty DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY qty DESC) AS drnk,
       NTILE(2)     OVER (PARTITION BY category_id ORDER BY qty DESC) AS tile
FROM sold
WHERE category_id IN (11, 13)
ORDER BY category_id, qty DESC, name;

WITH city_sales AS (
  SELECT shipping_city AS city, SUM(total_amount) AS amt
  FROM orders WHERE status <> 'CANCELLED'
  GROUP BY shipping_city
)
SELECT city, amt,
       SUM(amt) OVER ()                       AS grand_total,
       ROUND(amt / SUM(amt) OVER () * 100, 2) AS pct,
       ROUND(AVG(amt) OVER (), 0)             AS avg_city_amt,
       COUNT(*) OVER ()                       AS city_cnt
FROM city_sales
ORDER BY amt DESC;

WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       SUM(amt) OVER ()            AS sum_no_order,   -- 파티션 전체 합
       SUM(amt) OVER (ORDER BY ym) AS running_sum     -- 누적 합 (기본 프레임!)
FROM m ORDER BY ym;

WITH sold AS (
  SELECT p.name, SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'CANCELLED' AND p.category_id = 11
  GROUP BY p.name
)
SELECT name, qty,
       SUM(qty) OVER (ORDER BY qty DESC
                      ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
       SUM(qty) OVER (ORDER BY qty DESC
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range
FROM sold
ORDER BY qty DESC, name;

WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       ROUND(AVG(amt) OVER (ORDER BY ym
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS ma3,
       COUNT(*) OVER (ORDER BY ym
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)           AS window_rows
FROM m ORDER BY ym;

SELECT name, price,
       FIRST_VALUE(name) OVER (ORDER BY price DESC) AS first_v,
       LAST_VALUE(name)  OVER (ORDER BY price DESC) AS last_v_TRAP,   -- 함정!
       LAST_VALUE(name)  OVER (ORDER BY price DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND UNBOUNDED FOLLOWING) AS last_v_FIXED
FROM products
WHERE category_id = 21
ORDER BY price DESC;

LAST_VALUE(name) OVER (ORDER BY price DESC
                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       LAG(amt)  OVER (ORDER BY ym)      AS prev_amt,
       amt - LAG(amt) OVER (ORDER BY ym) AS diff,
       CONCAT(ROUND((amt - LAG(amt) OVER (ORDER BY ym))
                    / LAG(amt) OVER (ORDER BY ym) * 100, 1), '%') AS mom_pct,
       LEAD(amt) OVER (ORDER BY ym)      AS next_amt
FROM m ORDER BY ym;

SELECT category_id, name, price,
       FIRST_VALUE(name) OVER w AS top1,
       NTH_VALUE(name, 2) OVER (PARTITION BY category_id ORDER BY price DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING) AS top2
FROM products
WHERE category_id IN (13, 21)
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC)
ORDER BY category_id, price DESC;

WITH rev AS (
  SELECT c.name AS category, p.name AS product,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders     o ON o.order_id    = oi.order_id
  JOIN products   p ON p.product_id  = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  WHERE o.status <> 'CANCELLED'
  GROUP BY c.name, p.name
),
ranked AS (
  SELECT rev.*,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM rev
)
SELECT category, rn, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

SELECT dept, name, salary,
       ROW_NUMBER() OVER w                                    AS rn,
       SUM(salary)  OVER w                                    AS running_sum,
       SUM(salary)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND UNBOUNDED FOLLOWING) AS dept_total,
       LAG(salary)  OVER w                                    AS prev_salary
FROM employees
WHERE dept = '개발본부'
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)
ORDER BY salary DESC;

-- 누적합
SUM(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- 3구간 이동평균
AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- 중심 이동평균 (앞뒤 1칸씩)
AVG(x) OVER (ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
-- 파티션 전체 (LAST_VALUE 용)
LAST_VALUE(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- =====================================================================
-- Step 17 — 윈도우 함수 (Window Functions)  practice.sql
-- ---------------------------------------------------------------------
-- 실행:
--   mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop --table < practice.sql
--
-- 윈도우 함수는 MySQL 8.0 에서 처음 도입된 기능입니다. (5.7 에는 없습니다)
-- 이 스텝은 SELECT 만 합니다. 어떤 테이블도 변경하지 않습니다.
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [17-1] GROUP BY 는 행을 "접고", 윈도우 함수는 행을 "남긴다"
-- ---------------------------------------------------------------------
-- (A) GROUP BY : 카테고리당 1행으로 줄어든다. 개별 상품명은 사라진다.
SELECT category_id,
       COUNT(*)   AS cnt,
       AVG(price) AS avg_price
FROM products
WHERE category_id IN (21, 22)
GROUP BY category_id;

-- (B) 윈도우 함수 : 상품 행은 그대로 두고, 옆에 카테고리 평균을 "붙인다".
SELECT product_id,
       category_id,
       name,
       price,
       AVG(price) OVER (PARTITION BY category_id) AS cat_avg_price,
       price - AVG(price) OVER (PARTITION BY category_id) AS diff_from_avg
FROM products
WHERE category_id IN (21, 22)
ORDER BY category_id, price DESC;

-- ---------------------------------------------------------------------
-- [17-2] OVER () — 빈 괄호는 "결과 집합 전체"가 하나의 윈도우
-- ---------------------------------------------------------------------
SELECT product_id,
       name,
       price,
       SUM(price)   OVER () AS total_price,
       COUNT(*)     OVER () AS row_cnt,
       ROUND(price / SUM(price) OVER () * 100, 1) AS pct
FROM products
WHERE category_id = 13
ORDER BY price DESC;

-- ---------------------------------------------------------------------
-- [17-3] PARTITION BY — 윈도우를 그룹으로 쪼갠다
-- ---------------------------------------------------------------------
-- 사원의 급여를 "부서 안에서" 비교한다.
SELECT dept,
       name,
       salary,
       MAX(salary) OVER (PARTITION BY dept)                  AS dept_max,
       ROUND(salary / MAX(salary) OVER (PARTITION BY dept), 2) AS ratio_to_top
FROM employees
WHERE dept IN ('개발본부', '영업본부')
ORDER BY dept, salary DESC;

-- ---------------------------------------------------------------------
-- [17-4] 순위 함수 : ROW_NUMBER / RANK / DENSE_RANK / NTILE
-- ---------------------------------------------------------------------
-- 상품별 판매 수량(취소 제외)을 카테고리 안에서 순위 매기기.
-- 동점(TIE)이 있을 때 세 함수가 어떻게 다른지 보세요.
WITH sold AS (
  SELECT p.category_id,
         p.product_id,
         p.name,
         SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'CANCELLED'
  GROUP BY p.category_id, p.product_id, p.name
)
SELECT category_id,
       name,
       qty,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY qty DESC) AS rn,
       RANK()       OVER (PARTITION BY category_id ORDER BY qty DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY qty DESC) AS drnk,
       NTILE(2)     OVER (PARTITION BY category_id ORDER BY qty DESC) AS tile
FROM sold
WHERE category_id IN (11, 13)
ORDER BY category_id, qty DESC, name;

-- ---------------------------------------------------------------------
-- [17-5] 집계 윈도우 : SUM / AVG / COUNT / MIN / MAX ... OVER
-- ---------------------------------------------------------------------
-- 도시별 매출과, 전체 매출에서 차지하는 비중.
WITH city_sales AS (
  SELECT shipping_city AS city, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
  GROUP BY shipping_city
)
SELECT city,
       amt,
       SUM(amt) OVER ()                              AS grand_total,
       ROUND(amt / SUM(amt) OVER () * 100, 2)        AS pct,
       ROUND(AVG(amt) OVER (), 0)                    AS avg_city_amt,
       COUNT(*) OVER ()                              AS city_cnt
FROM city_sales
ORDER BY amt DESC;

-- ---------------------------------------------------------------------
-- [17-6] 프레임 절 (1) : ORDER BY 를 쓰면 기본 프레임이 자동으로 붙는다
-- ---------------------------------------------------------------------
--   ORDER BY 없음  → 기본 프레임 = 파티션 전체     (전체 합계)
--   ORDER BY 있음  → 기본 프레임 = RANGE BETWEEN
--                       UNBOUNDED PRECEDING AND CURRENT ROW  (누적 합계!)
--
-- 그래서 아래 sum_no_order 와 running_sum 이 완전히 다릅니다.
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
         SUM(total_amount)                AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym,
       amt,
       SUM(amt) OVER ()                 AS sum_no_order,   -- 파티션 전체 합
       SUM(amt) OVER (ORDER BY ym)      AS running_sum     -- 누적 합 (기본 프레임)
FROM m
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [17-7] 프레임 절 (2) : ROWS vs RANGE — 동점(peer)이 있을 때 갈린다
-- ---------------------------------------------------------------------
--   ROWS  ... : "물리적인 행" 개수로 센다.  현재 행 = 딱 그 한 행.
--   RANGE ... : "값이 같은 행(peer)"을 한 덩어리로 본다. 동점은 통째로 포함.
--
-- 판매수량 60/60/60/45 처럼 동점이 있는 데이터로 확인합니다.
WITH sold AS (
  SELECT p.name, SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'CANCELLED' AND p.category_id = 11
  GROUP BY p.name
)
SELECT name,
       qty,
       SUM(qty) OVER (ORDER BY qty DESC
                      ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
       SUM(qty) OVER (ORDER BY qty DESC
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range
FROM sold
ORDER BY qty DESC, name;

-- ---------------------------------------------------------------------
-- [17-8] 프레임 절 (3) : 이동 평균 (moving average)
-- ---------------------------------------------------------------------
-- 최근 3개월(자신 포함) 이동 평균 = ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
         SUM(total_amount)                AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym,
       amt,
       ROUND(AVG(amt) OVER (ORDER BY ym
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS ma3,
       COUNT(*) OVER (ORDER BY ym
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)           AS window_rows
FROM m
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [17-9] ★ 유명한 함정 : LAST_VALUE 가 "마지막 값"을 주지 않는다
-- ---------------------------------------------------------------------
-- ORDER BY 가 있으면 기본 프레임이 ... AND CURRENT ROW 이므로
-- LAST_VALUE 의 "마지막"은 곧 "현재 행"이 됩니다. (= 자기 자신)
-- 해결: 프레임을 UNBOUNDED FOLLOWING 까지 명시적으로 넓힌다.
SELECT name,
       price,
       FIRST_VALUE(name) OVER (ORDER BY price DESC)  AS first_v,
       LAST_VALUE(name)  OVER (ORDER BY price DESC)  AS last_v_TRAP,   -- 함정!
       LAST_VALUE(name)  OVER (ORDER BY price DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND UNBOUNDED FOLLOWING) AS last_v_FIXED
FROM products
WHERE category_id = 21
ORDER BY price DESC;

-- ---------------------------------------------------------------------
-- [17-10] 오프셋 함수 : LAG / LEAD — 전월 대비 증감률
-- ---------------------------------------------------------------------
--   LAG(expr, n, default)  : n 행 앞(과거)의 값
--   LEAD(expr, n, default) : n 행 뒤(미래)의 값
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym,
         SUM(total_amount)                AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
  GROUP BY ym
)
SELECT ym,
       amt,
       LAG(amt)  OVER (ORDER BY ym)                        AS prev_amt,
       amt - LAG(amt) OVER (ORDER BY ym)                   AS diff,
       CONCAT(ROUND((amt - LAG(amt) OVER (ORDER BY ym))
                    / LAG(amt) OVER (ORDER BY ym) * 100, 1), '%') AS mom_pct,
       LEAD(amt) OVER (ORDER BY ym)                        AS next_amt
FROM m
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [17-11] 오프셋 함수 : FIRST_VALUE / NTH_VALUE
-- ---------------------------------------------------------------------
-- 카테고리 안에서 1위 상품 / 2위 상품 이름을 모든 행에 붙인다.
-- FIRST_VALUE 는 기본 프레임에서도 잘 동작하지만(프레임 시작 = 파티션 시작),
-- NTH_VALUE 는 LAST_VALUE 와 똑같은 함정이 있으므로 프레임을 넓혀야 합니다.
SELECT category_id,
       name,
       price,
       FIRST_VALUE(name) OVER w AS top1,
       NTH_VALUE(name, 2) OVER (PARTITION BY category_id ORDER BY price DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING) AS top2
FROM products
WHERE category_id IN (13, 21)
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC)
ORDER BY category_id, price DESC;

-- ---------------------------------------------------------------------
-- [17-12] 실전 : 그룹별 TOP-N (카테고리별 매출 상위 2개 상품)
-- ---------------------------------------------------------------------
-- 윈도우 함수는 WHERE 절에서 쓸 수 없습니다 (SELECT 다음에 평가되므로).
-- → CTE/서브쿼리로 한 번 감싼 뒤 바깥에서 필터링합니다. 이것이 정석 패턴입니다.
WITH rev AS (
  SELECT c.name           AS category,
         p.name           AS product,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders     o ON o.order_id   = oi.order_id
  JOIN products   p ON p.product_id = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  WHERE o.status <> 'CANCELLED'
  GROUP BY c.name, p.name
),
ranked AS (
  SELECT rev.*,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM rev
)
SELECT category, rn, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

-- ---------------------------------------------------------------------
-- [17-13] 명명된 윈도우 (WINDOW 절) — 같은 OVER 를 반복하지 않기
-- ---------------------------------------------------------------------
-- WINDOW 절은 SELECT 뒤, ORDER BY 앞에 위치합니다.
-- 이름 붙인 윈도우를 상속해서 프레임만 바꿔 쓸 수도 있습니다: OVER (w ROWS ...)
SELECT dept,
       name,
       salary,
       ROW_NUMBER() OVER w                                    AS rn,
       SUM(salary)  OVER w                                    AS running_sum,
       SUM(salary)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND UNBOUNDED FOLLOWING) AS dept_total,
       LAG(salary)  OVER w                                    AS prev_salary
FROM employees
WHERE dept = '개발본부'
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)
ORDER BY salary DESC;

-- ---------------------------------------------------------------------
-- [17-14] 정리용 : 하나의 쿼리에 모아보기 (고객 등급별 구매 리포트)
-- ---------------------------------------------------------------------
WITH cust AS (
  SELECT c.grade, c.name, SUM(o.total_amount) AS amt
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.grade, c.name
)
SELECT grade,
       name,
       amt,
       RANK()       OVER w                            AS rank_in_grade,
       ROUND(amt / SUM(amt) OVER (PARTITION BY grade) * 100, 1) AS pct_in_grade,
       ROUND(AVG(amt) OVER (PARTITION BY grade), 0)   AS grade_avg,
       NTILE(4)     OVER (ORDER BY amt DESC)          AS quartile_overall
FROM cust
WINDOW w AS (PARTITION BY grade ORDER BY amt DESC)
ORDER BY grade, amt DESC
LIMIT 12;

-- =====================================================================
-- Step 17 — 윈도우 함수  exercise.sql
-- ---------------------------------------------------------------------
-- 아래 8문제를 직접 풀어 보세요. 정답/해설은 solution.sql 에 있습니다.
-- 모두 SELECT 문입니다. 테이블을 변경하지 마세요.
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [문제 1] 고객별 총 구매액 순위
--   DELIVERED 주문만 대상으로, 고객 이름 / 등급 / 총 구매액 과
--   전체 순위(rnk)를 구하세요. 동점이면 같은 순위를 주고 다음 순위는 건너뜁니다.
--   상위 10명만 출력.
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 2] 두 개의 순위를 동시에
--   문제 1의 결과에 "등급 안에서의 순위(rank_in_grade)"를 추가하세요.
--   또 등급별 평균 구매액(grade_avg)도 같이 보여주세요.
--   힌트: 명명된 윈도우(WINDOW 절)를 쓰면 깔끔합니다.
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 3] 카테고리별 매출 1위 상품 (TOP-N 패턴)
--   취소되지 않은 주문 기준, 카테고리명 / 1위 상품명 / 매출액을 구하세요.
--   힌트: 윈도우 함수는 WHERE 에서 못 씁니다. CTE 로 감싸세요.
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 4] 2025년 월별 매출 누적합
--   2025년(취소 제외) 월별 매출과 누적 매출을 구하세요.
--   프레임을 명시적으로 쓰세요 (ROWS BETWEEN ...).
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 5] 5개월 이동평균
--   2025년 월별 매출에 대해, 자신 포함 최근 5개월 이동평균을 구하세요.
--   프레임에 실제로 몇 개 행이 들어 있는지(window_rows)도 같이 보여주세요.
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 6] 전월 대비 증감률 + 그 해 최고 매출 월  ★ LAST_VALUE 함정
--   2025년 월별 매출에 대해:
--     - 전월 대비 증감률 (mom_pct)
--     - 그 해 매출이 가장 높은 달의 매출액 (best_amt)
--   를 구하세요. best_amt 는 모든 행에서 같은 값이어야 합니다.
--   힌트: ORDER BY amt 로 정렬한 LAST_VALUE 를 쓰되, 프레임을 넓혀야 합니다.
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 7] 주문 간격
--   customer_id = 1 (김민수)의 모든 주문을 날짜순으로 나열하고,
--   직전 주문일(prev_date)과 그 사이 며칠이 지났는지(days_gap)를 구하세요.
--   힌트: LAG + DATEDIFF
-- ---------------------------------------------------------------------
-- 여기에 작성


-- ---------------------------------------------------------------------
-- [문제 8] NTILE 로 고객 4분위 나누기
--   DELIVERED 기준 고객별 구매액을 내림차순으로 4분위(quartile)로 나누고,
--   분위별 고객 수 / 평균 구매액 / 최소~최대 구매액을 집계하세요.
--   힌트: NTILE 결과를 CTE 에 담고 바깥에서 GROUP BY.
-- ---------------------------------------------------------------------
-- 여기에 작성

-- =====================================================================
-- Step 17 — 윈도우 함수  solution.sql  (정답 + 해설)
-- ---------------------------------------------------------------------
-- 실행:
--   mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop --table < solution.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [정답 1] 고객별 총 구매액 순위
--
-- 해설:
--   먼저 CTE 에서 GROUP BY 로 고객별 합계를 "접고",
--   그 결과 집합에 RANK() 를 씌웁니다.
--   RANK 를 쓰라고 한 이유는 "동점이면 같은 순위, 다음은 건너뜀" 이기 때문.
--   ROW_NUMBER 를 쓰면 동점자에게 임의로 다른 번호가 붙어 순위표로는 부적절합니다.
-- ---------------------------------------------------------------------
WITH cust AS (
  SELECT c.customer_id, c.name, c.grade, SUM(o.total_amount) AS amt
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.customer_id, c.name, c.grade
)
SELECT RANK() OVER (ORDER BY amt DESC) AS rnk,
       name, grade, amt
FROM cust
ORDER BY rnk
LIMIT 10;

-- ---------------------------------------------------------------------
-- [정답 2] 등급 안 순위 + 전체 순위 + 등급 평균
--
-- 해설:
--   서로 다른 윈도우가 3개 필요합니다.
--     - 전체 순위        : ORDER BY amt DESC          (PARTITION 없음)
--     - 등급 내 순위     : PARTITION BY grade ORDER BY amt DESC
--     - 등급 평균        : PARTITION BY grade         (ORDER BY 없음! 있으면 누적평균이 됨)
--   grade_avg 에 ORDER BY 를 넣으면 기본 프레임 때문에 "누적 평균"이 되어버립니다.
--   이것이 17-5 에서 배운 함정입니다.
-- ---------------------------------------------------------------------
WITH cust AS (
  SELECT c.name, c.grade, SUM(o.total_amount) AS amt
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.name, c.grade
)
SELECT grade,
       name,
       amt,
       RANK() OVER wg                    AS rank_in_grade,
       RANK() OVER (ORDER BY amt DESC)   AS rank_overall,
       ROUND(AVG(amt) OVER (PARTITION BY grade), 0) AS grade_avg
FROM cust
WINDOW wg AS (PARTITION BY grade ORDER BY amt DESC)
ORDER BY grade, rank_in_grade
LIMIT 12;

-- ---------------------------------------------------------------------
-- [정답 3] 카테고리별 매출 1위 상품
--
-- 해설:
--   TOP-N 의 정석. 3단 구조입니다.
--     1) rev    : GROUP BY 로 상품별 매출 집계
--     2) ranked : ROW_NUMBER 로 카테고리 내 순위 부여
--     3) 바깥   : WHERE rn = 1 로 필터
--   2)와 3)을 한 번에 못 하는 이유 = 윈도우 함수는 WHERE 에서 평가 불가.
-- ---------------------------------------------------------------------
WITH rev AS (
  SELECT c.name AS category, p.name AS product,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders     o ON o.order_id    = oi.order_id
  JOIN products   p ON p.product_id  = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  WHERE o.status <> 'CANCELLED'
  GROUP BY c.name, p.name
),
ranked AS (
  SELECT rev.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM rev
)
SELECT category, product, revenue
FROM ranked
WHERE rn = 1
ORDER BY revenue DESC;

-- ---------------------------------------------------------------------
-- [정답 4] 2025년 월별 매출 누적합
--
-- 해설:
--   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 누적합의 표준형입니다.
--   사실 ORDER BY 만 써도 기본 프레임이 같은 효과를 내지만(RANGE 기준),
--   "읽는 사람이 의도를 알 수 있도록" 명시하는 습관이 좋습니다.
-- ---------------------------------------------------------------------
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       SUM(amt) OVER (ORDER BY ym
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM m
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [정답 5] 5개월 이동평균
--
-- 해설:
--   ROWS BETWEEN 4 PRECEDING AND CURRENT ROW  = 자신 포함 5개.
--   window_rows 를 같이 뽑아 보면 1~4월은 행이 모자라 1,2,3,4개뿐인 걸 알 수 있습니다.
--   → 이 구간의 "5개월 이동평균"은 5개월 평균이 아닙니다. 리포트에서 잘라내야 합니다.
-- ---------------------------------------------------------------------
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       ROUND(AVG(amt) OVER w, 0) AS ma5,
       COUNT(*)       OVER w     AS window_rows
FROM m
WINDOW w AS (ORDER BY ym ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [정답 6] 전월 대비 증감률 + 최고 매출 월  ★ LAST_VALUE 함정
--
-- 해설:
--   핵심은 best_amt 입니다.
--     LAST_VALUE(amt) OVER (ORDER BY amt)            ← 틀림! 기본 프레임이 CURRENT ROW 까지라
--                                                       매 행마다 자기 자신을 반환합니다.
--     LAST_VALUE(amt) OVER (ORDER BY amt
--       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  ← 정답
--   물론 MAX(amt) OVER () 로도 같은 값을 얻을 수 있고 그게 더 간단합니다.
--   여기서는 함정을 눈으로 보라고 일부러 LAST_VALUE 를 씁니다. (bad 컬럼과 비교)
-- ---------------------------------------------------------------------
WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(total_amount) AS amt
  FROM orders
  WHERE status <> 'CANCELLED'
    AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
  GROUP BY ym
)
SELECT ym, amt,
       CONCAT(ROUND((amt - LAG(amt) OVER (ORDER BY ym))
                    / LAG(amt) OVER (ORDER BY ym) * 100, 1), '%')  AS mom_pct,
       LAST_VALUE(amt) OVER (ORDER BY amt
                             ROWS BETWEEN UNBOUNDED PRECEDING
                                      AND UNBOUNDED FOLLOWING)      AS best_amt,
       MAX(amt) OVER ()                                             AS best_amt_simple,
       LAST_VALUE(amt) OVER (ORDER BY amt)                          AS best_amt_BAD
FROM m
ORDER BY ym;

-- ---------------------------------------------------------------------
-- [정답 7] 주문 간격
--
-- 해설:
--   LAG 로 이전 행의 order_date 를 가져온 뒤 DATEDIFF.
--   서브쿼리 없이 이전 행에 접근할 수 있다는 것이 윈도우 함수의 큰 장점입니다.
--   (MySQL 5.7 이었다면 셀프 조인이나 사용자 변수 트릭이 필요했습니다)
-- ---------------------------------------------------------------------
SELECT order_id,
       order_date,
       LAG(order_date) OVER w AS prev_date,
       DATEDIFF(order_date, LAG(order_date) OVER w) AS days_gap,
       total_amount
FROM orders
WHERE customer_id = 1
WINDOW w AS (ORDER BY order_date)
ORDER BY order_date;

-- ---------------------------------------------------------------------
-- [정답 8] NTILE 로 고객 4분위
--
-- 해설:
--   NTILE(4) 는 정렬 후 행을 4등분합니다. (행 수가 안 나눠떨어지면 앞쪽 버킷이 1개씩 더 가짐)
--   "금액을 4등분" 이 아니라 "행 수를 4등분" 이라는 점에 주의하세요.
--   금액 구간으로 나누고 싶다면 NTILE 이 아니라 CASE WHEN 이나 WIDTH_BUCKET 류의 계산이 필요합니다.
-- ---------------------------------------------------------------------
WITH cust AS (
  SELECT c.name, SUM(o.total_amount) AS amt
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.name
),
q AS (
  SELECT name, amt, NTILE(4) OVER (ORDER BY amt DESC) AS quartile
  FROM cust
)
SELECT quartile,
       COUNT(*)          AS customers,
       ROUND(AVG(amt),0) AS avg_amt,
       MIN(amt)          AS min_amt,
       MAX(amt)          AS max_amt
FROM q
GROUP BY quartile
ORDER BY quartile;