종합 문제

SELECT name, brand, price
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

SELECT name, last_login_at
FROM customers
WHERE grade = 'VIP'
  AND last_login_at IS NOT NULL
ORDER BY last_login_at DESC
LIMIT 10;

SELECT order_number, total_amount, cancelled_at
FROM orders
WHERE ordered_at LIKE '2024%'
  AND cancelled_at IS NOT NULL
ORDER BY cancelled_at DESC
LIMIT 10;

SELECT COUNT(*) AS high_rating_count,
       ROUND(AVG(rating), 2) AS avg_rating
FROM reviews
WHERE rating >= 4;

SELECT brand, COUNT(*) AS product_count
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING COUNT(*) >= 10
ORDER BY product_count DESC;

SELECT CASE grade
           WHEN 'VIP' THEN 'VIP'
           WHEN 'GOLD' THEN '골드'
           WHEN 'SILVER' THEN '실버'
           WHEN 'BRONZE' THEN '브론즈'
       END AS grade_kr,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY grade
ORDER BY avg_points DESC;

SELECT method,
       COUNT(*) AS payment_count,
       ROUND(SUM(amount)) AS total_amount
FROM payments
GROUP BY method
HAVING SUM(amount) >= 1000000000
ORDER BY total_amount DESC;

SELECT grade, COUNT(*) AS customer_count
FROM customers
WHERE acquisition_channel IS NULL
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 status,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(COUNT(notes) * 100.0 / COUNT(*), 1) AS notes_rate_pct
FROM orders
GROUP BY status
ORDER BY order_count DESC;

SELECT CASE
           WHEN stock_qty = 0 THEN '품절'
           WHEN stock_qty <= 10 THEN '부족'
           WHEN stock_qty <= 100 THEN '보통'
           ELSE '충분'
       END AS stock_status,
       COUNT(*) AS product_count,
       ROUND(AVG(price)) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY stock_status
ORDER BY product_count DESC;

SELECT brand,
       COUNT(*) AS product_count,
       ROUND(AVG(price)) AS avg_price,
       ROUND(AVG((price - cost_price) * 100.0 / price), 1) AS avg_margin_pct
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING COUNT(*) >= 5
ORDER BY avg_margin_pct DESC;

SELECT SUBSTR(ordered_at, 1, 4) AS year,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue,
       ROUND(AVG(total_amount)) AS avg_amount,
       COUNT(cancelled_at) AS cancelled_count
FROM orders
GROUP BY year
ORDER BY year;

SELECT COALESCE(acquisition_channel, '미분류') AS channel,
       COUNT(*) AS customer_count,
       ROUND(SUM(CASE WHEN grade = 'VIP' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS vip_rate_pct,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY COALESCE(acquisition_channel, '미분류')
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,
       ROUND(AVG(total_amount)) AS avg_amount
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

SELECT rating,
       CASE rating
           WHEN 5 THEN '최고'
           WHEN 4 THEN '좋음'
           WHEN 3 THEN '보통'
           WHEN 2 THEN '별로'
           WHEN 1 THEN '최악'
       END AS rating_label,
       COUNT(*) AS review_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
       ROUND(COUNT(title) * 100.0 / COUNT(*), 1) AS title_rate_pct
FROM reviews
GROUP BY rating
ORDER BY rating 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,
       ROUND(SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS discontinued_pct
FROM products
GROUP BY price_tier
ORDER BY product_count DESC;

SELECT grade,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points,
       ROUND(COUNT(gender) * 100.0 / COUNT(*), 1) AS gender_rate_pct,
       ROUND(COUNT(last_login_at) * 100.0 / COUNT(*), 1) AS login_rate_pct
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 status IN ('pending', 'paid', 'preparing') THEN '처리중'
           WHEN status IN ('shipped', 'delivered', 'confirmed') THEN '완료'
           ELSE '취소/반품'
       END AS status_group,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue,
       ROUND(AVG(shipping_fee)) AS avg_shipping,
       SUM(CASE WHEN point_used > 0 THEN 1 ELSE 0 END) AS point_used_count
FROM orders
GROUP BY status_group
ORDER BY order_count DESC;

SELECT card_issuer,
       COUNT(*) AS payment_count,
       ROUND(AVG(amount)) AS avg_amount,
       ROUND(SUM(CASE WHEN installment_months > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS installment_rate_pct
FROM payments
WHERE method = 'card'
  AND card_issuer IS NOT NULL
GROUP BY card_issuer
HAVING COUNT(*) >= 100
ORDER BY payment_count DESC;

SELECT supplier_id,
       COUNT(*) AS product_count,
       SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_count,
       ROUND(SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS active_rate_pct
FROM products
GROUP BY supplier_id
HAVING COUNT(*) >= 3
ORDER BY active_rate_pct DESC;

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

SELECT SUBSTR(ordered_at, 1, 7) AS month,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount)) AS total_revenue,
       ROUND(AVG(total_amount)) AS avg_amount,
       ROUND(COUNT(cancelled_at) * 100.0 / COUNT(*), 1) AS cancel_rate_pct,
       ROUND(SUM(CASE WHEN shipping_fee = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS free_ship_pct
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

SELECT grade,
       CASE
           WHEN is_active = 0 THEN '탈퇴'
           WHEN last_login_at IS NULL THEN '휴면'
           ELSE '활동'
       END AS activity_status,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY grade, activity_status
HAVING COUNT(*) >= 100
ORDER BY CASE grade
             WHEN 'VIP' THEN 1
             WHEN 'GOLD' THEN 2
             WHEN 'SILVER' THEN 3
             WHEN 'BRONZE' THEN 4
         END,
         customer_count DESC;

SELECT brand,
       COUNT(*) AS product_count,
       ROUND((COUNT(*) - COUNT(description)) * 100.0 / COUNT(*), 1) AS desc_missing_pct,
       ROUND((COUNT(*) - COUNT(specs)) * 100.0 / COUNT(*), 1) AS specs_missing_pct,
       ROUND((COUNT(*) - COUNT(weight_grams)) * 100.0 / COUNT(*), 1) AS weight_missing_pct
FROM products
GROUP BY brand
HAVING COUNT(*) >= 10
ORDER BY (
    (COUNT(*) - COUNT(description)) +
    (COUNT(*) - COUNT(specs)) +
    (COUNT(*) - COUNT(weight_grams))
) * 1.0 / COUNT(*) DESC;

SELECT SUBSTR(created_at, 1, 4) AS join_year,
       COUNT(*) AS customer_count,
       ROUND(SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS male_pct,
       ROUND(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS female_pct,
       ROUND(SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS unknown_pct,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
GROUP BY join_year
ORDER BY join_year;

SELECT CASE WHEN discount_amount > 0 THEN '할인있음' ELSE '할인없음' END AS has_discount,
       CASE
           WHEN total_amount < 50000 THEN '소액'
           WHEN total_amount < 200000 THEN '일반'
           WHEN total_amount < 1000000 THEN '고액'
           ELSE 'VIP급'
       END AS amount_tier,
       COUNT(*) AS order_count,
       ROUND(AVG(total_amount)) AS avg_amount
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY has_discount, amount_tier
ORDER BY has_discount, avg_amount DESC;

SELECT category_id,
       COUNT(*) AS total,
       SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_count,
       SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) AS discontinued_count,
       SUM(CASE WHEN stock_qty = 0 THEN 1 ELSE 0 END) AS out_of_stock,
       ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 5
ORDER BY total 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(SUM(amount)) AS total_amount,
       ROUND(AVG(amount)) AS avg_amount,
       COUNT(refunded_at) AS refund_count,
       ROUND(COUNT(refunded_at) * 100.0 / COUNT(*), 1) AS refund_rate_pct
FROM payments
GROUP BY method
HAVING COUNT(*) >= 1000
ORDER BY total_amount DESC;

SELECT (birth_date IS NOT NULL)
     + (gender IS NOT NULL)
     + (last_login_at IS NOT NULL)
     + (acquisition_channel IS NOT NULL) AS completeness,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points,
       ROUND(SUM(CASE WHEN grade = 'VIP' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS vip_rate_pct
FROM customers
GROUP BY completeness
ORDER BY completeness;

SELECT brand,
       COUNT(*) AS active_count,
       ROUND(AVG(price)) AS avg_price,
       ROUND(AVG((price - cost_price) * 100.0 / price), 1) AS avg_margin_pct,
       CASE
           WHEN AVG(price) >= 1000000 THEN '프리미엄'
           WHEN AVG(price) >= 300000 THEN '대중'
           ELSE '보급형'
       END AS brand_tier,
       SUM(CASE WHEN stock_qty <= 10 THEN 1 ELSE 0 END) AS low_stock_count
FROM products
WHERE is_active = 1
GROUP BY brand
HAVING COUNT(*) >= 5
ORDER BY active_count DESC
LIMIT 10;