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

문제

2017년 1월 한 달 동안 발생한 주문의 배송 예측이 정확했는지 분석을 하려고 합니다.

고객의 구매 일자별로 배송 예정 시각 안에 고객에게 도착한 주문과, 배송 예정 시각이 지나서 고객에게 도착한 주문을 각각 집계하는 쿼리를 작성해주세요. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외합니다.

계산 결과는 구매 날짜를 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

배송 예정일 예측 성공 : 배송 도착 시간 < 배송 예정 시각
배송 예정일 예측 실패 : 배송 도착 시간 > 배송 예정 시각

 

풀이

WITH t AS (
  SELECT STRFTIME('%Y-%m-%d', order_purchase_timestamp) AS purchase_date
    , CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 'success' ELSE 'fail' END as status
  FROM olist_orders_dataset
  WHERE purchase_date LIKE '2017-01%' 
    AND order_delivered_customer_date IS NOT NULL
    AND order_estimated_delivery_date IS NOT NULL
)

SELECT purchase_date
  , COUNT(CASE WHEN status = 'success' then 1 end) as 'success'
  , COUNT(CASE WHEN status = 'fail' then 1 end) as 'fail'
FROM t
GROUP BY 1
ORDER BY 1

1. 임시 테이블
- CASE WHEN THEN 구문을 활용해 '배송 도착 시간' < '배송 예측 시간' 인 주문 건수는 success로, 그 외는 fail로 표시
- 2017년 1월 자료만
- '배송 도착 시간'과 '배송 예측 시간'이 NULL 값이 아닌 것

2. 본 쿼리
: COUNT 조건을 사용해 각각 success, fail 인 주문 건수들의 개수를 구했다. 

 

배운 점

COUNT 조건
: 조건 여러개의 숫자를 구할 수 있다. (조건에 해당이 안되면 0으로 출력된다)
COUNT(CASE WHEN 칼럼명 = 1 THEN 1 END) AS 별칭