실전 문제 풀이 (7)

문제 1 — 고객별 최신 주문과 직전 주문 비교

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (customer_id, order_date, amount) VALUES
(1,'2025-07-01 10:00:00', 100.00),
(1,'2025-07-10 09:30:00', 150.00),
(1,'2025-07-20 12:00:00', 200.00),
(2,'2025-07-05 14:00:00', 50.00),
(2,'2025-06-01 12:00:00', 80.00),
(3,'2025-07-20 11:00:00', 120.00),
(3,'2025-07-20 11:00:00', 120.00),
(3,'2025-06-10 08:00:00', 100.00),
(4,'2025-07-22 09:00:00', 500.00);

 

요구사항
고객별로 가장 최근 주문과 그 직전 주문을 한 행에 표시하고, 두 주문 간 날짜 차이와 금액 변화를 계산하세요.

• 결과 컬럼: customer_id, recent_order_id, recent_order_date, recent_amount, prev_order_id, prev_order_date, prev_amount, days_between, amount_change
• 오직 이전 주문이 존재하는 고객만 출력
• amount_change 절댓값이 큰 순서로 정렬, 동률 시 customer_id 오름차순

WITH rank_table AS (
  SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
      ) AS rn
  FROM orders
  )
  

SELECT
  r1.customer_id,
  r1.order_date AS recent_order_date,
  r1.amount AS recent_amount,
  r2.order_date AS prev_order_date,
  r2.amount AS prev_amount,
  DATEDIFF(r1.order_date, r2.order_date) AS days_between,
  r1.amount - r2.amount AS amount_change
FROM rank_table r1
JOIN rank_table r2
  ON r1.customer_id = r2.customer_id
  AND r1.rn = r2.rn - 1
WHERE r1.rn = 1
ORDER BY ABS(amount_change) DESC, customer_id

 

 

 

문제 2 — 주문 취소율이 높은 고객 찾기

DROP TABLE IF EXISTS customer_orders;
CREATE TABLE customer_orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20) -- 'completed', 'cancelled'
);

INSERT INTO customer_orders (order_id, customer_id, order_date, status) VALUES
(1, 1, '2025-07-01', 'completed'),
(2, 1, '2025-07-02', 'cancelled'),
(3, 1, '2025-07-03', 'completed'),
(4, 1, '2025-07-04', 'cancelled'),
(5, 2, '2025-07-01', 'completed'),
(6, 2, '2025-07-02', 'cancelled'),
(7, 2, '2025-07-03', 'cancelled'),
(8, 2, '2025-07-04', 'completed'),
(9, 2, '2025-07-05', 'cancelled'),
(10, 3, '2025-07-01', 'completed'),
(11, 3, '2025-07-02', 'completed'),
(12, 3, '2025-07-03', 'completed'),
(13, 4, '2025-07-01', 'cancelled'),
(14, 4, '2025-07-02', 'cancelled'),
(15, 4, '2025-07-03', 'cancelled');

 

요구사항
고객별 전체 주문 건수, 취소 건수, 취소율(%)을 구하고 취소율이 높은 순으로 정렬하세요.

• 결과 컬럼: customer_id, total_orders, cancelled_orders, cancel_rate
• 주문이 3건 이상인 고객만 포함
• 취소율은 소수점 둘째 자리까지 표시
• 취소율이 같은 경우 total_orders가 많은 순서로 정렬

WITH total_order_table AS (
  SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
  FROM customer_orders
  GROUP BY customer_id
  ),
  
cancle_pct_table AS (
  SELECT
    *,
    cancelled_orders / total_orders * 100 AS cancle_rate
  FROM total_order_table
  )


SELECT 
  customer_id,
  total_orders,
  cancelled_orders,
  ROUND(cancle_rate, 2) AS cancle_rate
FROM cancle_pct_table
WHERE total_orders >= 3
ORDER BY cancle_rate DESC, total_orders DESC

 

 

 

문제 3 — 월별 상품 매출 상위 3개와 누적 비중

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  store_id INT,
  sale_date DATE,
  units INT,
  unit_price DECIMAL(10,2)
);

INSERT INTO sales (product_id, store_id, sale_date, units, unit_price) VALUES
(101,1,'2025-05-10', 10, 20.00),
(101,2,'2025-05-12',  5, 20.00),
(102,1,'2025-05-11', 20, 10.00),
(103,1,'2025-05-15',  2,100.00),
(104,1,'2025-05-20', 50, 2.00),
(101,1,'2025-06-05', 30, 20.00),
(102,1,'2025-06-10', 10, 10.00),
(103,1,'2025-06-12',  5,100.00),
(104,1,'2025-06-25',100, 2.00),
(101,1,'2025-07-02', 25, 20.00),
(102,1,'2025-07-03', 40, 10.00),
(103,1,'2025-07-05', 10,100.00),
(104,1,'2025-07-08', 80, 2.00),
(105,1,'2025-07-09',  1,500.00);

 

요구사항
월 단위로 상품별 총 판매량과 총 매출을 계산하고, 각 월 매출 상위 3개 상품과 그 누적 매출 비중을 구하세요.

• 결과 컬럼: month, product_id, total_units, total_revenue, revenue_rank, cumulative_revenue_pct
• 월별 매출 3위까지 출력
• month 오름차순, revenue_rank 오름차순 정렬

WITH product_month_sales AS (
  SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    product_id,
    SUM(units * unit_price) AS total_revenue
  FROM sales
  GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), product_id
),

rank_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY month
      ORDER BY total_revenue DESC
      ) AS revenue_rank,
    SUM(total_revenue) OVER (
      PARTITION BY month
      ) AS month_total_revenue
  FROM product_month_sales
),

pct_table AS (
  SELECT
    *,
    SUM(total_revenue) OVER (
      PARTITION BY month
      ORDER BY total_revenue DESC
      ) / month_total_revenue * 100 AS cumulative_revenue_pct
  FROM rank_table
)

SELECT
  month,
  product_id,
  total_revenue,
  revenue_rank,
  cumulative_revenue_pct
FROM pct_table
WHERE revenue_rank <= 3
ORDER BY month, revenue_rank

 

 

---

 

GPT 5로 바뀌고 문제 난이도도 급격히 상승했다. 결국 한 문제도 못풀어서 GPT한테 답물어보고 뜯어보는 식으로 공부했다. 시간이 꽤 오래걸린다. 그래서 앞으로는 이런 난이도의 문제로 3개정도씩만 풀어보려고 한다...