실전 문제 풀이 (72) - 사용자별 첫 구매 이후 행동 / 주력 고객층 파악 / 구매 지속성 분류

문제 1 — 사용자별 첫 구매 이후 행동 분석

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

INSERT INTO orders_m2 VALUES
(1, 1, '2025-01-05', 100),
(2, 1, '2025-01-20',  50),
(3, 1, '2025-02-10', 200),
(4, 2, '2025-01-07',  30),
(5, 2, '2025-03-01',  70),
(6, 3, '2025-02-15', 120),
(7, 4, '2025-01-10',  40),
(8, 4, '2025-01-12',  60),
(9, 4, '2025-01-30', 100);

목표

각 사용자에 대해 첫 구매일 이후의 구매 행동을 요약하라.
단, 첫 구매 자체는 “이후 행동”에서 제외한다.

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

user_id	사용자 ID
first_order_date	해당 사용자의 첫 구매일
orders_after_first	첫 구매일 이후 주문 횟수
revenue_after_first	첫 구매일 이후 총 매출
days_to_second_order	첫 구매일과 두 번째 구매일 사이의 일 수 (두 번째 구매가 없으면 NULL)

WITH window_table AS (
  SELECT
    user_id,
    order_date,
    amount,
    MIN(order_date) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ) AS first_order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ) AS rn
  FROM orders_m2
  )
  
SELECT
  user_id,
  first_order_date,
  SUM(CASE
    WHEN order_date > first_order_date
    THEN 1
    ELSE 0
    END) AS order_after_first,
  SUM(CASE
      WHEN order_date > first_order_date
      THEN amount
      ELSE 0
      END) AS revenue_after_first,
  MAX(CASE
      WHEN rn = 2
      THEN DATEDIFF(order_date, first_order_date)
      ELSE NULL
      END) AS days_to_seconde_order    
FROM window_table
GROUP BY user_id, first_order_date

 

 

 

문제 2 — 상품 카테고리별 “주력 고객층” 파악

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

INSERT INTO orders_m3 VALUES
(1, 1, 'A', '2025-06-01', 100),
(2, 1, 'A', '2025-06-10', 150),
(3, 2, 'A', '2025-06-15', 200),
(4, 3, 'A', '2025-06-20',  50),
(5, 1, 'B', '2025-06-05', 300),
(6, 2, 'B', '2025-06-07', 100),
(7, 2, 'B', '2025-06-25', 100),
(8, 4, 'B', '2025-06-30',  80);

목표

각 카테고리별로 해당 카테고리 매출의 50% 이상을 차지하는 최소 사용자 수와
그 사용자들의 매출 기여도를 계산하라.

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

category	상품 카테고리
core_user_count	누적 매출이 상위부터 합산했을 때 전체 매출의 50% 이상을 만드는 최소 사용자 수
core_revenue	해당 사용자들의 매출 합
total_revenue	해당 카테고리 전체 매출
core_revenue_ratio	core_revenue / total_revenue

WITH window_table AS (
  SELECT
    user_id,
    category,
    amount,
    SUM(amount) OVER (
      PARTITION BY category
      ORDER BY amount DESC
      ) AS cumul_revenue,
    SUM(amount) OVER (
      PARTITION BY category
      ) AS total_revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY amount DESC
      ) AS rn
  FROM orders_m3
  )
  
SELECT
  category,
  MIN(CASE
  WHEN cumul_revenue / total_revenue >= 0.5
  THEN rn
  ELSE NULL
  END) AS core_user_count,
  MIN(CASE
  WHEN cumul_revenue / total_revenue >= 0.5
  THEN cumul_revenue
  ELSE NULL
  END) AS core_revenue,
  total_revenue,
  ROUND(MIN(CASE
  WHEN cumul_revenue / total_revenue >= 0.5
  THEN cumul_revenue
  ELSE NULL
  END) / total_revenue, 2) AS core_revenue_ratio
FROM window_table
GROUP BY category, total_revenue

 

 

 

문제 3 — 월별 사용자 “구매 지속성” 분류

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

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

목표

각 사용자·월 기준으로 사용자를 아래 기준으로 분류하라.

• NEW: 해당 월이 첫 구매 월
• CONTINUED: 직전 월에도 구매 이력이 있는 경우
• RETURNED: 과거에 구매 이력은 있으나 직전 월에는 구매가 없는 경우

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

month	YYYY-MM
user_id	사용자 ID
user_status	NEW / CONTINUED / RETURNED

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01') AS month,
  MIN(DATE_FORMAT(order_date, '%Y-%m-01')) OVER (
    PARTITION BY user_id
    ) AS first_order_month
  FROM orders_m4
  )
  
SELECT
  m1.user_id,
  m1.month,
  CASE
  WHEN m1.month = m1.first_order_month
  THEN 'NEW'
  WHEN m2.user_id IS NOT NULL
  THEN 'CONTINUED'
  WHEN m2.user_id IS NULL
   AND m1.month > m1.first_order_month
  THEN 'RETURNED'
  END AS user_status
FROM month_table m1
LEFT JOIN month_table m2
  ON m1.user_id = m2.user_id
  AND m2.month = DATE_SUB(m1.month, INTERVAL 1 MONTH)

 

 

 

---

 

2번 문제를 풀 때 카테고리별로만 집계를 내렸다. 하지만 문제에서 원하는 것은 카테고리별로 매출 50%가 넘으려면 몇명의 사용자가 필요한 지를 구해야 하는 것이기 때문에 시작할 때 group by를 통해 사용자별 매출을 미리 집계한 뒤 내가 작성한 쿼리대로 나아가야 했다.

WITH user_revenue AS (
  SELECT
    category,
    user_id,
    SUM(amount) AS user_revenue
  FROM orders_m3
  GROUP BY category, user_id
)