[solvesql] SQL 풀어보기 - 배송 예정일 예측 성공과 실패

 

✔️ 문제

2017년 1월 한 달 동안 발생한 주문의 배송 예측이 정확했는지 분석을 하려고 합니다. 고객의 구매 일자별로 배송 예정 시각 안에 고객에게 도착한 주문과, 배송 예정 시각이 지나서 고객에게 도착한 주문을 각각 집계하는 쿼리를 작성해주세요. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외합니다. 계산 결과는 구매 날짜를 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

 

✔️ LEVEL 3

 

📌 주요 요구사항

• 배송 예정 시각과 도착 시간을 비교하여 성공과 실패로 구분
• 날짜별 도착 시간과 배송 예정 시각을 비교하여 성공, 실패 개수 집계
• 배송 완료 또는 배송 예정 시각 데이터가 없는 경우 제외

SELECT STRFTIME("%Y-%m-%d",order_purchase_timestamp) purchase_date,
	COUNT(CASE WHEN order_delivered_customer_date < order_estimated_delivery_date THEN order_id  END) AS  success,
	COUNT(CASE WHEN order_delivered_customer_date >= order_estimated_delivery_date THEN order_id  END) AS fail

FROM olist_orders_dataset 
WHERE STRFTIME("%Y-%m",order_purchase_timestamp) = '2017-01' AND
	order_delivered_customer_date IS NOT NULL AND
	order_estimated_delivery_date IS NOT NULL 
GROUP BY purchase_date

 

✍ 코드 해석

1. 배송 성공과 실패 구분
   • order_delivered_customer_date(실제 배송 완료 시각)과 order_estimated_delivery_date(예상 배송 시각)를 비교하여 배송이 정확하게 이루어졌는지 확인.
   • 배송이 예정 시각보다 빠르거나 같으면 성공, 늦으면 실패로 분류했다.
2. 날짜별로 집계
   • order_purchase_timestamp(주문 일시)를 기준으로 연-월-일 형식으로 변환하여 그룹화했다.
   • 날짜별 배송 성공 건수와 실패 건수를 각각 COUNT 함수와 CASE WHEN 문을 이용하여 계산했다.
3. 2017년 1월만 추출
   • WHERE STRFTIME("%Y-%m",order_purchase_timestamp) = '2017-01' 로 조건에 맞는 날짜만 조회
4. NULL 값 처리
   • 배송 완료 시각(order_delivered_customer_date)과 예상 배송 시각(order_estimated_delivery_date)이 NULL인 경우는 분석에서 제외했다.

 

📌 실행 결과

• 27개의 데이터가 추출되었다
• 날짜 별 배송 성공 갯수, 배송 실패 갯수 출력.