[SQL/프로그래머스] LV3. 배송 예정일 예측 성공과 실패

https://solvesql.com/problems/estimated-delivery-date/

https://solvesql.com/problems/estimated-delivery-date/

 

🚀 1. 문제 상황

전자상거래 플랫폼에서 주문 데이터를 분석하여 날짜별 배송 성공 및 실패 건수를 확인하려고 합니다. 구체적으로는 다음과 같은 기준으로 데이터를 집계하려고 합니다:

• 배송 실패(Fail): 실제 배송일(order_delivered_customer_date)이 예상 배송일(order_estimated_delivery_date)보다 늦은 경우
• 배송 성공(Success): 실제 배송일이 예상 배송일과 같거나 더 빠른 경우

또한, 2017년 1월에 발생한 주문 데이터만 분석하려고 합니다.

 

SELECT 
  DATE(o1.order_purchase_timestamp) AS purchase_date,
  COUNT(CASE WHEN o1.order_estimated_delivery_date < o1.order_delivered_customer_date THEN 1 END) AS fail,
  COUNT(CASE WHEN o1.order_estimated_delivery_date >= o1.order_delivered_customer_date THEN 1 END) AS success
FROM 
  olist_orders_dataset AS o1
WHERE 
  o1.order_purchase_timestamp LIKE '2017-01%' 
  AND o1.order_delivered_customer_date IS NOT NULL
GROUP BY 
  DATE(o1.order_purchase_timestamp)
ORDER BY
  purchase_date;

 

1️⃣ 날짜 추출 및 그룹화

• DATE(o1.order_purchase_timestamp)
  • DATETIME 형식의 order_purchase_timestamp에서 날짜 부분(YYYY-MM-DD)만 추출합니다.
• GROUP BY DATE(o1.order_purchase_timestamp)
  • 날짜별로 데이터를 그룹화하여 각 날짜의 배송 성공 및 실패 건수를 계산합니다.

2️⃣ 배송 성공 및 실패 건수 계산

casewhen

• CASE 조건을 만족하면 1을 반환하고, COUNT는 1의 개수를 셉니다.
• 조건을 만족하지 않는 행은 NULL로 처리되며 COUNT에 포함되지 않습니다.

주의사항

• 조건 순서 중요
  • CASE WHEN은 위에서부터 순차적으로 조건을 확인합니다.
  • 첫 번째로 만족하는 조건만 실행되므로, 더 포괄적인 조건은 아래에 작성해야 합니다.
• NULL 처리 주의
  • NULL 값은 =, != 연산자로 비교할 수 없습니다.
  • IS NULL, IS NOT NULL을 사용해야 합니다.

 

• 배송 실패(fail) 계산: COUNT(CASE WHEN o1.order_estimated_delivery_date < o1.order_delivered_customer_date THEN 1 END) AS fail
  • 예상 배송일보다 실제 배송일이 늦은 경우를 CASE 문으로 판단하고, 해당 행의 개수를 셉니다.
• 배송 성공(success) 계산: COUNT(CASE WHEN o1.order_estimated_delivery_date >= o1.order_delivered_customer_date THEN 1 END) AS success
  • 예상 배송일과 같거나 더 빠른 경우를 CASE 문으로 판단하고, 해당 행의 개수를 셉니다. 

3️⃣ 날짜 필터링

• WHERE o1.order_purchase_timestamp LIKE '2017-01%'
  • order_purchase_timestamp가 2017년 1월에 해당하는 데이터만 필터링합니다.
• AND o1.order_delivered_customer_date IS NOT NULL
  • order_delivered_customer_date가 NULL인 행(배송되지 않은 데이터)은 제외합니다.

4️⃣ 정렬

• ORDER BY purchase_date
  • 결과를 날짜 순서(purchase_date)대로 정렬합니다.