[TIL154] solvesql - 배송예정일 예측 성공과 실패

배송 예정일 예측 성공과 실패

문제 설명

2017년 1월에 구매된 주문을 대상으로 배송 예측이 얼마나 정확했는지 분석하는 문제이다.

주문 데이터에는 고객이 상품을 구매한 시각, 실제 배송 완료 시각, 그리고 주문 시점에 계산된 배송 예정 시각이 저장되어 있다.

구매 날짜별로 다음 두 가지를 집계해야 한다.

• 배송 예정 시각 이전(또는 동일 시각)에 배송이 완료된 주문 수 (success)
• 배송 예정 시각 이후에 배송이 완료된 주문 수 (fail)

단, 실제 배송 완료 시각 또는 배송 예정 시각이 없는 주문은 계산에서 제외한다.

결과는 구매 날짜 기준 오름차순으로 정렬한다.

---

내가 작성한 코드

SELECT DISTINCT customer_id,
DATE_FORMAT(order_purchase_timestamp,'%Y-%m-%d') AS purchase_date,
SUM(
    CASE
        WHEN order_delivered_customer_date <= order_estimated_delivery_date
        THEN 1
        ELSE 0
    END
) AS success,
SUM(
    CASE
        WHEN order_delivered_customer_date > order_estimated_delivery_date
        THEN 1
        ELSE 0
    END
) AS fail
FROM olist_orders_dataset
WHERE order_purchase_timestamp BETWEEN '2017-01-01' AND '2017-01-31'
  AND order_status = 'delivered'
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY customer_id, purchase_date
ORDER BY purchase_date;

---

틀린 이유

1. customer_id 기준으로 그룹화함

문제는

구매 날짜별 성공/실패 건수

를 요구한다.

하지만

GROUP BY customer_id, purchase_date

를 사용하여 고객별 집계가 되어버렸다.

즉,

2017-01-01 성공 10건

이 아니라

고객 A의 2017-01-01 주문
고객 B의 2017-01-01 주문
...

처럼 집계 기준이 달라진다.

---

2. 날짜 필터가 위험함

BETWEEN '2017-01-01' AND '2017-01-31'

을 사용했다.

order_purchase_timestamp는 DATETIME 타입이다.

예를 들어

2017-01-31 15:30:00

은 포함되지 않을 수 있다.

 

실무에서는 보통

>= '2017-01-01'
AND < '2017-02-01'

패턴을 사용한다.

---

정답 코드

SELECT
    DATE(order_purchase_timestamp) AS purchase_date,

    SUM(
        CASE
            WHEN order_delivered_customer_date
                 <= order_estimated_delivery_date
            THEN 1
            ELSE 0
        END
    ) AS success,

    SUM(
        CASE
            WHEN order_delivered_customer_date
                 > order_estimated_delivery_date
            THEN 1
            ELSE 0
        END
    ) AS fail

FROM olist_orders_dataset

WHERE order_purchase_timestamp >= '2017-01-01'
  AND order_purchase_timestamp < '2017-02-01'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL

GROUP BY DATE(order_purchase_timestamp)

ORDER BY purchase_date;

---

핵심 개념

조건부 집계 (Conditional Aggregation)

특정 조건을 만족하는 행의 개수를 세는 패턴이다.

SUM(
    CASE
        WHEN 조건
        THEN 1
        ELSE 0
    END
)

---

성공 건수

SUM(
    CASE
        WHEN 배송완료일 <= 배송예정일
        THEN 1
        ELSE 0
    END
)

---

기억할 패턴

-- 성공 건수
SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)

-- 실패 건수
SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)

-- 성공률
ROUND(
    SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)
    * 100.0
    / COUNT(*)
, 2)

---

한 줄 요약

이 문제는 구매 날짜별로 배송 성공/실패 건수를 집계하는 조건부 집계 문제이며, SUM(CASE WHEN ...) 패턴과 GROUP BY 날짜를 정확히 적용하는 것이 핵심이다.