solvesql | 난이도 3 | 배송 예정일 예측 성공과 실패💡

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

✔️0217 복습

 

Brazilian E-Commerce Public Dataset by Olist 데이터셋을 활용!

: 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터

 

문제 조건

1. 2017년 1월 한 달 동안 발생한 주문의 배송 예측

2. 고객의 구매 일자별로 배송 예정 시각 안에 도착한 주문과, 그렇지 않은 주문을 각각 집계

3. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우 제외

4. 주문 날짜를 기준으로 오름차순 정렬

 

Solution

-- (1)
SELECT DATE(order_purchase_timestamp) AS purchase_date
     , IFNULL(SUM(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 1 END), 0) AS success
     , IFNULL(SUM(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date THEN 1 END), 0) AS fail
FROM olist_orders_dataset
WHERE order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date
ORDER BY purchase_date

-- (2)
SELECT DATE(order_purchase_timestamp) AS purchase_date
     , COALESCE(COUNT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 1 END), 0) AS success
     , COALESCE(COUNT(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date THEN 1 END), 0) AS fail
FROM olist_orders_dataset
WHERE order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date
ORDER BY purchase_date

-- (3)
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 -- 0으로 표기하고 싶지 않으면 ELSE 부분 없어도 무방
     , 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 00:00:00' AND '2017-01-31 23:59:59'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date
ORDER BY purchase_date

/*
💡 아래 쿼리를 다시 보니 너무 비효율적임 
-- case 구문에 집계 함수 사용하는 것이 익숙하기 전···

WITH preprocessing AS (
SELECT DATE(order_purchase_timestamp) AS purchase_date,
       CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date 
       	    THEN 1 ELSE 0 END AS is_success 
FROM olist_orders_dataset
WHERE DATE(order_purchase_timestamp) BETWEEN '2017-01-01' AND '2017-01-31' 
  AND order_delivered_customer_date IS NOT NULL 
  AND order_estimated_delivery_date IS NOT NULL
)
SELECT purchase_date,
       COUNT(CASE WHEN is_success = 1 THEN is_success END) AS success,
       COUNT(CASE WHEN is_success = 0 THEN is_success END) AS fail
FROM preprocessing 
GROUP BY purchase_date
ORDER BY purchase_date
*/

 

✅ 특정 기간에 발생한 주문 필터링:

• WHERE절에서 컬럼을 가공하는 함수를 직접 사용하는 것은 성능을 저하시킬 수 있기 때문에, 범위 조건을 사용하여 2017년 1월 한 달 동안의 발생한 주문을 필터링한다.
• 배송 예정 시각 또는 완료 시각 데이터가 없는 경우는 분석에서 제외한다. 

 

✅ 배송 성공 여부 분석:

• order_delivered_customer_date가 order_estimated_delivery_date보다 작거나 같으면 배송이 제시간에 완료된 것으로 간주하고, CASE 구문과 집계 함수를 적절히 활용한다.
• 결괏값이 null인 경우 0으로 출력하기 위해 IFNULL 또는 COALESCE 함수를 활용한다.

 

✅ 결과를 날짜별로 정렬:

• 결과를 purchase_date 기준으로 오름차순 정렬하여, 각 날짜별로 성공과 실패 건수를 확인할 수 있도록 한다.

 

피드백

이와 같은 분석을 통해 일별 배송 성공/실패 비율을 파악하여 배송 프로세스 개선을 위한 기초 자료로 활용할 수 있지 않을까 생각한다.