CASE Expressions

SELECT name,
       CASE grade
           WHEN 'BRONZE' THEN '브론즈'
           WHEN 'SILVER' THEN '실버'
           WHEN 'GOLD' THEN '골드'
           WHEN 'VIP' THEN 'VIP'
       END AS grade_kr
FROM customers
LIMIT 10;

SELECT name, price,
       CASE
           WHEN price < 100000 THEN '저가'
           WHEN price < 500000 THEN '중저가'
           WHEN price < 1000000 THEN '중가'
           ELSE '고가'
       END AS price_tier
FROM products
LIMIT 10;

SELECT order_number,
       CASE status
           WHEN 'pending' THEN '주문접수'
           WHEN 'paid' THEN '결제완료'
           WHEN 'preparing' THEN '준비중'
           WHEN 'shipped' THEN '배송중'
           WHEN 'delivered' THEN '배송완료'
           WHEN 'confirmed' THEN '구매확정'
           WHEN 'cancelled' THEN '취소'
           WHEN 'return_requested' THEN '반품요청'
           WHEN 'returned' THEN '반품완료'
       END AS status_kr
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;

SELECT rating,
       CASE rating
           WHEN 5 THEN '최고'
           WHEN 4 THEN '좋음'
           WHEN 3 THEN '보통'
           WHEN 2 THEN '별로'
           WHEN 1 THEN '최악'
       END AS rating_text,
       title
FROM reviews
LIMIT 10;

SELECT name, stock_qty,
       CASE
           WHEN stock_qty = 0 THEN '품절'
           WHEN stock_qty <= 10 THEN '부족'
           WHEN stock_qty <= 100 THEN '보통'
           ELSE '충분'
       END AS stock_status
FROM products
LIMIT 10;

SELECT order_id,
       CASE method
           WHEN 'card' THEN '신용카드'
           WHEN 'bank_transfer' THEN '계좌이체'
           WHEN 'virtual_account' THEN '가상계좌'
           WHEN 'kakao_pay' THEN '카카오페이'
           WHEN 'naver_pay' THEN '네이버페이'
           WHEN 'point' THEN '포인트'
       END AS method_kr,
       amount
FROM payments
LIMIT 10;

SELECT name, email,
       CASE is_active
           WHEN 1 THEN '활성'
           WHEN 0 THEN '탈퇴'
       END AS status
FROM customers
LIMIT 10;

SELECT order_number, total_amount,
       CASE
           WHEN total_amount < 50000 THEN '소액'
           WHEN total_amount < 200000 THEN '일반'
           WHEN total_amount < 1000000 THEN '고액'
           ELSE 'VIP급'
       END AS amount_tier
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;

SELECT name, price, is_active, stock_qty,
       CASE
           WHEN is_active = 0 THEN '단종'
           WHEN stock_qty = 0 THEN '품절'
           ELSE '판매중'
       END AS sale_status
FROM products
LIMIT 15;

SELECT name, point_balance,
       CASE
           WHEN point_balance = 0 THEN '없음'
           WHEN point_balance <= 5000 THEN '소액'
           WHEN point_balance <= 20000 THEN '보통'
           ELSE '고액'
       END AS point_level
FROM customers
LIMIT 10;

SELECT CASE
           WHEN price < 100000 THEN '저가'
           WHEN price < 500000 THEN '중저가'
           WHEN price < 1000000 THEN '중가'
           ELSE '고가'
       END AS price_tier,
       COUNT(*) AS product_count
FROM products
GROUP BY CASE
             WHEN price < 100000 THEN '저가'
             WHEN price < 500000 THEN '중저가'
             WHEN price < 1000000 THEN '중가'
             ELSE '고가'
         END
ORDER BY product_count DESC;

SELECT CASE
           WHEN status IN ('pending', 'paid', 'preparing') THEN '처리중'
           WHEN status IN ('shipped', 'delivered', 'confirmed') THEN '완료'
           WHEN status IN ('cancelled', 'return_requested', 'returned') THEN '취소/반품'
       END AS status_group,
       COUNT(*) AS order_count
FROM orders
GROUP BY status_group
ORDER BY order_count DESC;

SELECT CASE
           WHEN rating >= 4 THEN '긍정'
           WHEN rating = 3 THEN '보통'
           ELSE '부정'
       END AS sentiment,
       COUNT(*) AS review_count
FROM reviews
GROUP BY sentiment
ORDER BY review_count DESC;

SELECT CASE
           WHEN price < 100000 THEN '저가'
           WHEN price < 500000 THEN '중저가'
           WHEN price < 1000000 THEN '중가'
           ELSE '고가'
       END AS price_tier,
       COUNT(*) AS product_count,
       ROUND(AVG(stock_qty)) AS avg_stock
FROM products
GROUP BY price_tier
ORDER BY avg_stock DESC;

SELECT CASE
           WHEN gender IS NULL THEN '미입력'
           WHEN gender = 'M' THEN '남성'
           WHEN gender = 'F' THEN '여성'
       END AS gender_kr,
       COUNT(*) AS customer_count
FROM customers
GROUP BY gender_kr
ORDER BY customer_count DESC;

SELECT CASE method
           WHEN 'card' THEN '신용카드'
           WHEN 'bank_transfer' THEN '계좌이체'
           WHEN 'virtual_account' THEN '가상계좌'
           WHEN 'kakao_pay' THEN '카카오페이'
           WHEN 'naver_pay' THEN '네이버페이'
           WHEN 'point' THEN '포인트'
       END AS method_kr,
       COUNT(*) AS payment_count,
       ROUND(AVG(amount)) AS avg_amount
FROM payments
GROUP BY method
ORDER BY payment_count DESC;

SELECT name, price, stock_qty
FROM products
WHERE is_active = 1
ORDER BY CASE WHEN stock_qty = 0 THEN 1 ELSE 0 END,
         price ASC
LIMIT 15;

SELECT grade, COUNT(*) AS customer_count
FROM customers
GROUP BY grade
ORDER BY CASE grade
             WHEN 'VIP' THEN 1
             WHEN 'GOLD' THEN 2
             WHEN 'SILVER' THEN 3
             WHEN 'BRONZE' THEN 4
         END;

SELECT CASE
           WHEN total_amount < 50000 THEN '5만원 미만'
           WHEN total_amount < 200000 THEN '5~20만원'
           WHEN total_amount < 1000000 THEN '20~100만원'
           ELSE '100만원 이상'
       END AS amount_tier,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue
FROM orders
GROUP BY amount_tier
ORDER BY total_revenue DESC;

SELECT CASE
           WHEN rating >= 4 THEN '긍정'
           WHEN rating = 3 THEN '보통'
           ELSE '부정'
       END AS sentiment,
       COUNT(*) AS review_count,
       ROUND(COUNT(title) * 100.0 / COUNT(*), 1) AS title_rate_pct
FROM reviews
GROUP BY sentiment
ORDER BY review_count DESC;

SELECT CASE
           WHEN (price - cost_price) * 100.0 / price < 10 THEN '저마진'
           WHEN (price - cost_price) * 100.0 / price < 20 THEN '표준'
           WHEN (price - cost_price) * 100.0 / price < 30 THEN '고마진'
           ELSE '프리미엄'
       END AS margin_tier,
       COUNT(*) AS product_count,
       ROUND(AVG((price - cost_price) * 100.0 / price), 1) AS avg_margin_pct
FROM products
WHERE is_active = 1
GROUP BY margin_tier
ORDER BY avg_margin_pct DESC;

SELECT SUBSTR(created_at, 1, 4) AS join_year,
       grade,
       COUNT(*) AS customer_count
FROM customers
WHERE SUBSTR(created_at, 1, 4) IN ('2023', '2024', '2025')
GROUP BY join_year, grade
ORDER BY join_year, CASE grade
                        WHEN 'VIP' THEN 1
                        WHEN 'GOLD' THEN 2
                        WHEN 'SILVER' THEN 3
                        WHEN 'BRONZE' THEN 4
                    END;

SELECT CASE
           WHEN total_amount < 50000 THEN '유료배송'
           ELSE '무료배송'
       END AS shipping_type,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(SUM(shipping_fee)) AS total_shipping_fee
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY shipping_type;

SELECT CASE status
           WHEN 'completed' THEN '완료'
           WHEN 'pending' THEN '대기'
           WHEN 'failed' THEN '실패'
           WHEN 'refunded' THEN '환불'
       END AS status_kr,
       COUNT(*) AS payment_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM payments
GROUP BY status
ORDER BY payment_count DESC;

SELECT CASE
           WHEN is_active = 0 THEN '탈퇴'
           WHEN last_login_at IS NULL THEN '휴면'
           ELSE '활동'
       END AS activity_status,
       COUNT(*) AS customer_count
FROM customers
GROUP BY activity_status
ORDER BY customer_count DESC;

SELECT SUBSTR(ordered_at, 1, 7) AS month,
       CASE
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (3, 4, 5) THEN '봄'
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (6, 7, 8) THEN '여름'
           WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) IN (9, 10, 11) THEN '가을'
           ELSE '겨울'
       END AS season,
       COUNT(*) AS order_count
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

SELECT brand,
       COUNT(*) AS product_count,
       ROUND(AVG(price)) AS avg_price,
       CASE
           WHEN AVG(price) >= 1000000 THEN '프리미엄 브랜드'
           WHEN AVG(price) >= 300000 THEN '대중 브랜드'
           ELSE '보급형 브랜드'
       END AS brand_tier
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING COUNT(*) >= 10
ORDER BY avg_price DESC;

SELECT CASE
           WHEN point_used > 0 THEN '포인트 사용'
           ELSE '포인트 미사용'
       END AS point_usage,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(AVG(discount_amount)) AS avg_discount
FROM orders
GROUP BY point_usage;

SELECT CASE
           WHEN is_active = 0 AND stock_qty > 0 THEN '단종+재고있음'
           WHEN is_active = 0 AND stock_qty = 0 THEN '단종+재고없음'
           WHEN is_active = 1 AND stock_qty > 0 THEN '판매중+재고있음'
           WHEN is_active = 1 AND stock_qty = 0 THEN '판매중+품절'
       END AS status_group,
       COUNT(*) AS product_count,
       ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY status_group
ORDER BY product_count DESC;

SELECT CASE
           WHEN installment_months IS NULL THEN '정보없음'
           WHEN installment_months = 0 THEN '일시불'
           WHEN installment_months <= 3 THEN '2~3개월'
           WHEN installment_months <= 6 THEN '4~6개월'
           ELSE '7개월 이상'
       END AS installment_tier,
       COUNT(*) AS payment_count,
       ROUND(AVG(amount)) AS avg_amount
FROM payments
WHERE method = 'card'
GROUP BY installment_tier
ORDER BY payment_count DESC;