실전 문제 풀이 (67) - 사용자별 최고 매출 이후 주문 / 월별 재구매 사용자 / 카테고리별 매출 비중

문제 1 — 사용자별 최고 매출 주문 이후 행동 분석

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

INSERT INTO orders_s1 VALUES
(1, 1, '2025-01-05', 100.00),
(2, 1, '2025-02-10', 400.00),
(3, 1, '2025-03-01', 200.00),
(4, 1, '2025-03-20',  50.00),
(5, 2, '2025-01-07', 300.00),
(6, 2, '2025-02-15', 100.00),
(7, 3, '2025-02-01', 500.00),
(8, 3, '2025-02-18', 500.00),
(9, 3, '2025-03-05', 100.00);

목표

각 사용자별로 가장 높은 주문 금액이 발생한 주문을 기준 시점으로 삼아,
그 이후에 발생한 주문 수와 이후 총매출을 계산하라.

단,

• 최고 매출 주문이 여러 건인 경우, 가장 빠른 주문 날짜를 기준으로 한다.
• 기준 주문 이후에 주문이 없다면 이후 주문 수와 매출은 0으로 출력한다.

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

• user_id : 사용자 ID
• max_order_date : 사용자별 최고 매출 주문 날짜 (동일 금액이면 가장 빠른 날짜)
• max_order_amount : 사용자별 최고 주문 금액
• orders_after_max : 기준 주문 이후 발생한 주문 수
• revenue_after_max : 기준 주문 이후 총매출

WITH max_order_table AS (
  SELECT
    user_id,
    order_date AS max_order_date,
    amount AS max_order_amount
  FROM (
    SELECT
      user_id,
      order_date,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ) AS rn
    FROM orders_s1
    ) AS tmp_table
  WHERE rn = 1
  )
  
SELECT
  m.user_id,
  m.max_order_date,
  m.max_order_amount,
  COUNT(o.user_id) AS orders_after_max,
  IFNULL(SUM(o.amount), 0) AS revenue_after_max
FROM max_order_table m 
LEFT JOIN orders_s1 o
  ON o.user_id = m.user_id
  AND o.order_date > m.max_order_date
GROUP BY m.user_id, m.max_order_date, m.max_order_amount

 

 

 

문제 2 — 월별 재구매 사용자 비율 계산

DROP TABLE IF EXISTS orders_s2;
CREATE TABLE orders_s2 (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE
);

INSERT INTO orders_s2 VALUES
(1, 1, '2025-06-05'),
(2, 2, '2025-06-10'),
(3, 1, '2025-07-01'),
(4, 3, '2025-07-03'),
(5, 2, '2025-07-15'),
(6, 4, '2025-07-20'),
(7, 1, '2025-08-02'),
(8, 2, '2025-08-10'),
(9, 5, '2025-08-12');

목표

각 월별로 전체 구매 사용자 수, 이전에 한 번이라도 구매 이력이 있는 사용자 수, 그리고 **재구매 사용자 비율(%)**을 계산하라.

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

• month : YYYY-MM
• total_users : 해당 월 구매 사용자 수 (중복 제거)
• returning_users : 해당 월 구매자 중 과거 구매 이력이 존재하는 사용자 수
• returning_user_ratio_pct : returning_users / total_users * 100 (소수 둘째 자리)

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01') AS month
  FROM orders_s2
  )
  
SELECT
  DATE_FORMAT(m1.month, '%Y-%m') AS month,
  COUNT(m1.user_id) AS total_users,
  COUNT(m2.user_id) AS returning_users,
  ROUND(COUNT(m2.user_id) / COUNT(m1.user_id) * 100, 2) AS returning_user_ratio_pct
FROM month_table m1
LEFT JOIN month_table m2
  ON m1.user_id = m2.user_id
  AND m2.month < m1.month
GROUP BY DATE_FORMAT(m1.month, '%Y-%m')

 

 

 

문제 3 — 상품 카테고리별 매출 기여도 분석

DROP TABLE IF EXISTS products_s3;
DROP TABLE IF EXISTS orders_s3;

CREATE TABLE products_s3 (
  product_id INT PRIMARY KEY,
  category VARCHAR(50)
);

CREATE TABLE orders_s3 (
  order_id INT PRIMARY KEY,
  product_id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);

INSERT INTO products_s3 VALUES
(1, 'Game'),
(2, 'Game'),
(3, 'Accessory'),
(4, 'Accessory'),
(5, 'Subscription');

INSERT INTO orders_s3 VALUES
(1, 1, '2025-07-01', 300.00),
(2, 2, '2025-07-02', 200.00),
(3, 3, '2025-07-03', 150.00),
(4, 4, '2025-07-10', 150.00),
(5, 5, '2025-07-15', 400.00);

목표

2025년 7월 기준으로 카테고리별 총매출, 전체 매출 대비 비중(%), 그리고 매출 비중 순위를 계산하라.

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

• category : 상품 카테고리
• category_revenue : 해당 카테고리 총매출
• revenue_share_pct : 전체 매출 대비 비중(%) (소수 둘째 자리)
• revenue_rank : 매출 비중 기준 순위 (1위가 가장 큼)

WITH order_table AS (
  SELECT
    product_id,
    order_date,
    amount
  FROM orders_s3
  WHERE order_date BETWEEN '2025-07-01' AND '2025-07-31'
  ),
  
category_revenue_table AS (  
  SELECT
    p.category,
    SUM(o.amount) AS category_revenue
  FROM products_s3 p
  LEFT JOIN order_table o
    ON p.product_id = o.product_id
  GROUP BY p.category
  )
  
SELECT
  *,
  ROUND(category_revenue / SUM(category_revenue) OVER () * 100, 2) AS revenue_share_pct,
  ROW_NUMBER() OVER (
    ORDER BY category_revenue DESC
    ) AS revenue_rank
FROM category_revenue_table

 

 

---

 

1번 문제에서 최고 주문금액을 계산하는 조건을 지적했다. 문제에서 최고 주문금액인 주문이 여러개라면 주문날짜가 가장 빠른 것을 우선으로 하라고 했는데, ROW_NUMBER의 ORDER BY를 통해 최고주문 내림차순은 지정했으나, 주문날짜 조건을 따로 지정하지 않았었다.

 

2번 문제에서 재구매 사용자를 집계하는 과정에서 DISTINCT를 사용하지 않았다. 나는 월별 구매 테이블을 만드는 과정에서 DISTINCT를 사용했기 때문에 최종 집계 에서 COUNT(DISTINCT)를 사용하지 않아도 될 것이라고 생각했으나, 조인하는 과정에서 과거 이력이 2개월 이상 나올 수 있기 때문에 COUNT(DISTINCT)를 사용해야 한다고 GPT가 지적했다.