실전 문제 풀이 (68) - 누적 구매가 80% 초과한 시점 / 재구매 사용자 평균 구매 가격 / 상위 20% 매출 사용자 매출 기여도 계산

문제 1 — 사용자별 누적 구매 비중이 80%를 초과한 첫 시점 찾기

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

INSERT INTO orders_p1 VALUES
(1, 1, '2025-01-05', 100),
(2, 1, '2025-01-20', 200),
(3, 1, '2025-02-10', 150),
(4, 1, '2025-03-01', 50),
(5, 2, '2025-01-03', 300),
(6, 2, '2025-02-15', 100),
(7, 2, '2025-03-10', 100),
(8, 3, '2025-01-07', 50),
(9, 3, '2025-01-15', 50),
(10,3, '2025-04-01', 400);

목표

각 사용자별로 시간 순서대로 누적 구매 금액을 계산했을 때,
해당 사용자의 전체 구매 금액 대비 누적 비중이 80%를 처음 초과하는 주문을 찾는다.

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

• user_id : 사용자 ID
• order_id : 조건을 처음 만족한 주문 ID
• order_date : 해당 주문 날짜
• amount : 해당 주문 금액
• cumulative_amount : 해당 주문까지의 누적 구매 금액
• total_amount : 해당 사용자의 전체 구매 금액
• cumulative_ratio : cumulative_amount / total_amount (소수 둘째 자리)

WITH window_table AS (
  SELECT
    user_id,
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
      PARTITION BY user_id
      ORDER BY order_date, order_id
    ) AS cumulative_amount,
    SUM(amount) OVER (
      PARTITION BY user_id
    ) AS total_amount
  FROM orders_p1
),

rank_table AS (
  SELECT
    *,
    cumulative_amount / total_amount AS cumulative_ratio,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date, order_id
    ) AS rn
  FROM window_table
  WHERE cumulative_amount / total_amount > 0.8
)

SELECT
  user_id,
  order_id,
  order_date,
  amount,
  cumulative_amount,
  total_amount,
  ROUND(cumulative_ratio, 2) AS cumulative_ratio
FROM rank_table
WHERE rn = 1
ORDER BY user_id;

 

 

 

문제 2 — 재구매 사용자 중 최초 구매 이후 평균 구매 간격 계산

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

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

목표

각 월별로 다음 조건을 만족하는 사용자들만 대상으로 분석한다.

• 해당 월 이전에 이미 구매 이력이 존재
• 해당 월에 구매를 2회 이상 수행

그 사용자들에 대해
**최초 구매일 이후 각 구매 사이의 평균 간격(일 단위)**을 계산하여라

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

• qualified_users : 재구매 조건(구매 2회 이상)을 만족한 사용자 ID
• avg_days_between_orders : 사용자별 평균 구매 간격을 다시 평균낸 값 (소수 둘째 자리)

WITH month_table AS (
  SELECT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01') AS month
  FROM orders_p2
  ),

flag_user_table AS (
  SELECT
    user_id,
    month
  FROM month_table m1
  WHERE EXISTS (
    SELECT 1
    FROM month_table m2
    WHERE m1.user_id = m2.user_id
      AND m2.month < m1.month
    )
  GROUP BY user_id, month
  HAVING COUNT(*) >= 2
  ),

days_between_order_table AS (
  SELECT
    o.user_id,
    o.order_date,
    DATEDIFF(o.order_date, 
             LAG(o.order_date) OVER (
             PARTITION BY o.user_id
             ORDER BY o.order_date
             )) AS days_between_orders
  FROM orders_p2 o
  JOIN flag_user_table f
    ON o.user_id = f.user_id
  )
  
SELECT
  user_id AS qualified_users,
  ROUND(AVG(days_between_orders), 2) AS avg_days_between_orders 
FROM days_between_order_table
GROUP BY user_id

 

 

 

문제 3 — 상품 카테고리별 상위 20% 매출 사용자 매출 기여도 계산

DROP TABLE IF EXISTS users_p3;
DROP TABLE IF EXISTS products_p3;
DROP TABLE IF EXISTS orders_p3;

CREATE TABLE users_p3 (
  user_id INT PRIMARY KEY,
  user_name VARCHAR(20)
);

CREATE TABLE products_p3 (
  product_id INT PRIMARY KEY,
  category VARCHAR(20)
);

CREATE TABLE orders_p3 (
  order_id INT PRIMARY KEY,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO users_p3 VALUES
(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

INSERT INTO products_p3 VALUES
(1,'Game'),(2,'Game'),(3,'Book'),(4,'Book');

INSERT INTO orders_p3 VALUES
(1,1,1,500),
(2,2,1,300),
(3,3,2,200),
(4,4,2,100),
(5,5,1,50),
(6,1,3,400),
(7,2,3,100),
(8,3,4,50);

목표

각 카테고리별로 사용자 매출을 집계한 뒤,

• 사용자 매출 기준 상위 20%에 해당하는 사용자들만 추출
• 그 사용자들의 매출 합이 카테고리 전체 매출에서 차지하는 비중을 계산한다

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

• category : 상품 카테고리
• top_user_count : 상위 20%에 포함된 사용자 수
• top_users_revenue : 해당 사용자들의 매출 합
• total_category_revenue : 카테고리 전체 매출
• revenue_share_pct : 매출 기여도 (%) 소수 둘째 자리

WITH category_user_revenue_table AS (
  SELECT
    p.category,
    o.user_id,
    SUM(amount) AS users_revenue
  FROM products_p3 p
  LEFT JOIN orders_p3 o
    ON p.product_id = o.product_id
  GROUP BY p.category, o.user_id
  ),

rank_table AS (
  SELECT
    *,
    COUNT(user_id) OVER (
      PARTITION BY category
      ) AS user_count,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY users_revenue DESC
      ) AS rn,
    SUM(users_revenue) OVER (
      PARTITION BY category
      ) AS total_category_revenue
  FROM category_user_revenue_table
  )
  
SELECT
  category,
  COUNT(*) AS top_user_count,
  SUM(users_revenue) AS top_users_revenue,
  AVG(total_category_revenue) AS total_category_revenue,
  ROUND(SUM(users_revenue) / AVG(total_category_revenue), 2) AS revenue_share_pct
FROM rank_table
WHERE rn <= CEIL(user_count * 0.2)
GROUP BY category

 

 

---

 

오늘 채점 결과는 아직 GPT가 부족하다는 것을 알 수 있었다.

 

1번 문제에서 피드백으로 초과 또는 이상이었다고 했는데, 문제에서는 초과라고 표현을 했었다. 자기가 한 말을 기억 못하고 있다.

 

2번 문제에서 GPT가 처음에 낸 문제에서 월별 출력 요구는 제거해달라고 요청했었는데 그걸 기억 못하고 처음 GPT가 문제를 냈던 내용을 기준으로 채점했다.