실전 문제 풀이 (69) - 월별 활성 사용자 유지 여부 판별 / 주문 금액 이상치 탐지 / 카테고리별 가장 큰 주문 추출

문제 1 — 월별 활성 사용자 유지 여부 판별

DROP TABLE IF EXISTS user_activity_m1;
CREATE TABLE user_activity_m1 (
  user_id INT,
  activity_date DATE
);

INSERT INTO user_activity_m1 (user_id, activity_date) VALUES
(1, '2025-06-05'),
(1, '2025-07-10'),
(1, '2025-09-01'),
(2, '2025-06-15'),
(2, '2025-07-03'),
(3, '2025-07-20'),
(4, '2025-08-05');

목표

각 사용자에 대해 활동이 있었던 월 기준으로

• 다음 달에도 활동이 있었는지 여부를 판단하여 출력하라.
• 중간 월이 비어 있어도 그대로 판단한다.

출력(요구사항) — 컬럼 설명

• user_id : 사용자 ID
• month : 활동 월 (YYYY-MM)
• has_activity_next_month : 다음 달에도 활동이 있으면 1, 없으면 0

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(activity_date, '%Y-%m-01') AS month
  FROM user_activity_m1
  )
  
SELECT
  user_id,
  month,
  IF(LEAD(month) OVER (
    PARTITION BY user_id
    ORDER BY month
    ) IS NOT NULL, 1, 0) AS has_activity_next_month
FROM month_table

 

 

 

문제 2 — 주문 금액 기준 이상치(Outlier) 탐지

DROP TABLE IF EXISTS orders_m2;
CREATE TABLE orders_m2 (
  order_id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO orders_m2 (order_id, user_id, amount) VALUES
(1, 1, 100.00),
(2, 2, 120.00),
(3, 3, 130.00),
(4, 4, 110.00),
(5, 5, 500.00),
(6, 6, 90.00);

목표

전체 주문 금액의 평균 대비 2배 이상인 주문을
이상치로 분류하여 출력하라.

출력(요구사항) — 컬럼 설명

• order_id : 주문 ID
• user_id : 사용자 ID
• amount : 주문 금액
• is_outlier : 평균 대비 2배 이상이면 1, 아니면 0

SELECT
  order_id,
  user_id,
  amount,
  IF((SELECT AVG(amount) * 2 FROM orders_m2) < amount, 1, 0) AS is_outlier
FROM orders_m2

 

 

 

문제 3 — 카테고리별 매출 집중도 분석

DROP TABLE IF EXISTS orders_m3;
CREATE TABLE orders_m3 (
  order_id INT PRIMARY KEY,
  category VARCHAR(20),
  amount DECIMAL(10,2)
);

INSERT INTO orders_m3 (order_id, category, amount) VALUES
(1, 'A', 300.00),
(2, 'A', 200.00),
(3, 'A', 100.00),
(4, 'B', 400.00),
(5, 'B', 50.00),
(6, 'C', 150.00);

목표

각 카테고리별로

• 전체 매출
• 가장 큰 단일 주문 금액
• 가장 큰 주문이 전체 매출에서 차지하는 비율(%)

을 계산하라.

출력(요구사항) — 컬럼 설명

• category : 상품 카테고리
• total_revenue : 카테고리 총매출
• max_order_amount : 해당 카테고리의 최대 주문 금액
• max_order_ratio_pct : 최대 주문 금액 / 총매출 × 100
  • 소수 둘째 자리까지 표시

SELECT
  category,
  SUM(amount) AS total_revenue,
  MAX(amount) AS max_order_amount,
  ROUND(MAX(amount) / SUM(amount) * 100, 2) AS max_order_ratio_pct
FROM orders_m3
GROUP BY category

 

 

---