Home [solvesql] 배송 예정일 예측 성공과 실패
Post
Cancel

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

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

출처

문제

Brazilian E-Commerce Public Dataset by Olist 데이터셋은 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 입니다. 그 중 olist_orders_dataset 테이블에는 주문 ID, 고객 ID, 주문 상태, 구매 시각 등 주문 내역 데이터가 들어있습니다. Olist의 주문부터 배송까지 프로세스는 다음 단계를 통해 이루어지고, 각 단계마다 시각을 기록하고 있습니다.

  1. 고객의 구매
    • order_purchase_timestamp 컬럼에 구매 시점이 저장됨
  2. 판매자가 주문을 승인
    • order_approved_at 컬럼에 승인 시점이 저장됨
  3. 택배사에 도착하여 배송 시작
    • order_delivered_carrier_date 컬럼에 배송 시작 시점이 저장됨
  4. 배송 완료
    • order_delivered_customer_date 컬럼에 배송 완료 시점이 저장됨

추가로 order_estimated_delivery_date 컬럼에는 주문 시점에 계산한 배송 예정 시각이 저장되어 있습니다.

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

  • purchase_date - 구매 날짜 (예: 2017-01-01)
  • success - 배송 예정 시각 안에 고객에게 도착한 주문 수
  • fail - 배송 예정 시각이 지나 고객에게 도착한 주문 수


결과 데이터 예시

purchase_datesuccessfail
2017-01-0640
2017-01-0731
  • 2017년 1월 6일 구매된 주문은 총 4건이고 모두 배송 예정일 안에 배송 완료됨
  • 2017년 1월 7일 구매된 주문은 총 4건이고 그 중 3건은 배송 예정일 안에 배송 완료되었으나, 1건은 배송 예정일 이후에 배송 완료됨

풀이

  • case when(조건) then (반환값) : 조건에 해당하는 값을 반환한다.
  • 조건에 맞는 값을 반환하여 그 값을 count로 측정하고 컬럼명 설정
  • 날짜 조건은 like % 사용
  • 어려운건 없었다!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select
  date(order_purchase_timestamp) as purchase_date,
  count(
    case
      when date(order_delivered_customer_date) < date(order_estimated_delivery_date) then order_id
    end
  ) as success,
  count(
    case
      when date(order_delivered_customer_date) >= date(order_estimated_delivery_date) then order_id
    end
  ) as fail
from
  olist_orders_dataset
where
  order_purchase_timestamp like "2017-01%"
group by
  purchase_date
order by
  purchase_date

gh-pages

This post is licensed under CC BY 4.0 by the author.

[solvesql] 멘토링 짝꿍 리스트

[Programmers] 자동차 대여 기록에서 장기 단기 대여 구분하기