[데이터베이스] SQL 연습문제

문제는 모두 주석에 적어놓았습니다!

 

-- 1번: 데이터 조회 및 조인 SQL
-- 고객별 총 주문 금액과 주문 횟수를 조회하는 쿼리
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,      -- 주문 횟수
    COALESCE(SUM(o.total_amount), 0) AS total_spent -- 총 주문 금액 (NULL 방지)
FROM day3_customers c
LEFT JOIN day3_orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;



-- 2번: 데이터 조회 및 조인 SQL
-- 카테고리별 총 판매액과 판매된 제품 수를 조회하는 쿼리
SELECT 
    p.category,
    COUNT(od.product_id) AS total_sold_products, -- 판매된 제품 수
    COALESCE(SUM(od.quantity * od.price), 0) AS total_sales -- 총 판매액
FROM day3_products p
LEFT JOIN day3_order_details od ON p.product_id = od.product_id
GROUP BY p.category;



-- 3번: 데이터 표준화
-- 제품 이름을 소문자로 변환하고 카테고리를 표준화하는 쿼리
UPDATE day3_products
SET 
    product_name = LOWER(product_name),  -- 제품명을 소문자로 변환
    category = CASE 
        WHEN category IN ('electronics', 'Electronic', 'ELECTRONICS') THEN 'Electronics'
        WHEN category IN ('clothing', 'Clothing', 'CLOTHING') THEN 'Clothing'
        WHEN category IN ('books', 'Books', 'BOOKS') THEN 'Books'
        ELSE category  -- 기존 값 유지
    END;

 

 

 

 

 

 

---------------------------------------------------------
-- 1) '전자기기' 카테고리 제품 가격 5% 인상
---------------------------------------------------------
UPDATE product AS p
JOIN category AS c 
  ON p.category_id = c.category_id
SET p.product_price = p.product_price * 1.05
WHERE c.category_name = '전자기기';

---------------------------------------------------------
-- 2) 2025년 1월 이전 가입한 고객의 
--    미완료 주문(order_status <> '배송완료') 상태를 '배송완료'로 변경
---------------------------------------------------------
UPDATE `order` AS o
JOIN member AS m 
  ON o.member_id = m.member_id
SET o.order_status = '배송완료'
WHERE m.created_at < '2025-01-01'
  AND o.order_status <> '배송완료';

---------------------------------------------------------
-- 3) 재고(product_stock) 10개 미만 상품이 들어간 주문 상세 삭제 후,
--    변경된 주문의 총액(total_price) 업데이트
---------------------------------------------------------
-- 3-1) 재고 부족 상품이 포함된 주문 상세 삭제
DELETE ol
FROM order_list AS ol
JOIN product AS p 
  ON ol.product_id = p.product_id
WHERE p.product_stock < 10;

-- 3-2) 주문 상세가 변경된 각 주문별 총액 재계산
UPDATE `order` AS o
SET o.total_price = (
  SELECT IFNULL(SUM(ol.subtotal_price), 0)
  FROM order_list ol
  WHERE ol.order_number = o.order_number
);

 

 

------------------------------------------------------
-- 1) 고객별 가장 자주 구매하는 카테고리
------------------------------------------------------
-- Step 1: 각 고객이 구매한 제품을 카테고리별로 그룹화하고,
--         COUNT(*)를 통해 “몇 번 구매했는지” 집계한다.
-- Step 2: WINDOW FUNCTION(ROW_NUMBER)을 이용하여,
--         해당 고객에게 가장 구매 횟수가 많은 카테고리만 추출한다.
WITH purchase_count AS (
    SELECT
        o.member_id,
        cat.category_name,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (
            PARTITION BY o.member_id          -- 고객별 파티션
            ORDER BY COUNT(*) DESC            -- 구매 횟수 많은 순으로 정렬
        ) AS rn
    FROM order_list    AS ol
    JOIN product       AS p   ON ol.product_id   = p.product_id
    JOIN category      AS cat ON p.category_id   = cat.category_id
    JOIN `order`       AS o   ON ol.order_number = o.order_number
    GROUP BY
        o.member_id,
        cat.category_name
)
SELECT
    member_id,
    category_name       AS most_frequent_category,
    cnt                 AS purchase_count
FROM purchase_count
WHERE rn = 1;  -- 가장 많이 구매한 카테고리만 출력


------------------------------------------------------
-- 2) 고객별 평균 주문 금액과 전체 평균 금액과의 차이
------------------------------------------------------
-- Step 1: 고객별 평균 주문금액(AVG(o.total_price)) 계산
-- Step 2: 전체 주문의 평균금액(서브쿼리 사용)과의 차이를 구함
SELECT
    o.member_id,
    AVG(o.total_price) AS avg_member_price,
    (
      AVG(o.total_price)
      - (SELECT AVG(total_price) FROM `order`)  -- 전체 평균
    ) AS diff_from_overall
FROM `order` AS o
GROUP BY o.member_id;


------------------------------------------------------
-- 3) 각 고객의 최근 주문과 이전 주문 사이의 평균 기간
------------------------------------------------------
-- Step 1: LAG()를 사용해서 이전 주문일(prev_order_date) 추출
-- Step 2: DATEDIFF()로 이전 주문과 현재 주문의 일수 차이 계산
-- Step 3: 고객별로 평균을 내서 ‘평균 주문 간격’을 구한다.
WITH order_with_prev AS (
    SELECT
        o.order_number,
        o.member_id,
        o.order_date,
        LAG(o.order_date) OVER (
            PARTITION BY o.member_id       -- 고객별 파티션
            ORDER BY o.order_date          -- 주문일 순
        ) AS prev_order_date
    FROM `order` AS o
)
SELECT
    member_id,
    AVG(DATEDIFF(order_date, prev_order_date)) AS avg_interval_days
FROM order_with_prev
WHERE prev_order_date IS NOT NULL      -- 첫 주문은 이전 주문이 없으므로 제외
GROUP BY member_id;