[SQL테스트]배송 예정일 예측 성공과 실패

문제

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

풀이

1번 풀이

WITH cte as (
  SELECT order_id,
    CASE
       WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 'success'
       WHEN order_delivered_customer_date > order_estimated_delivery_date THEN 'fail'
    END as success_fail
  FROM olist_orders_dataset
  WHERE 1 = 1
  AND order_purchase_timestamp LIKE '2017-01%'
)
SELECT
  SUBSTR(A.order_purchase_timestamp, 1, 10) AS purchase_date,
  SUM(CASE WHEN B.success_fail = 'success' THEN 1 ELSE 0 END) AS success,
  SUM(CASE WHEN B.success_fail = 'fail' THEN 1 ELSE 0 END) AS fail
FROM olist_orders_dataset A RIGHT JOIN cte B 
ON A.order_id = B.order_id
WHERE 1 = 1
AND A.order_status = 'delivered'
GROUP BY 1
ORDER BY 1

2번 풀이

SELECT 
  STRFTIME('%Y-%m-%d',order_purchase_timestamp) AS purchase_date,
  SUM(CASE WHEN order_estimated_delivery_date>=order_delivered_customer_date THEN 1 ELSE 0 END) AS success,
  SUM(CASE WHEN order_estimated_delivery_date<order_delivered_customer_date THEN 1 ELSE 0 END) AS fail
FROM olist_orders_dataset
WHERE 1=1
AND order_purchase_timestamp LIKE '2017-01%'
AND order_delivered_customer_date IS NOT NULL
GROUP BY 1
ORDER BY 1

출력 WHERE 절을 잘보면

AND A.order_status = 'delivered'

이런 구문이 있다...
저 구문 없이 돌렸는데 계속 에러나서 스터디 팀원분들이랑 이유를 찾았는데,
테이블을 조회해보니까 아예 배달이 안된 데이터도 있었다...

그래서 저 invoiced는 빼줌!

후기

join으로 푸니까 실행결과가 너무 오래걸린다.
다른 분들은 join없이 아래와 같이 풀었는데 엄청 빨리 풀림.

SELECT DATE(order_purchase_timestamp) AS purchase_date, 
COUNT(CASE WHEN order_estimated_delivery_date > order_delivered_customer_date THEN order_id END) AS success,
COUNT(CASE WHEN order_estimated_delivery_date <= order_delivered_customer_date THEN order_id END) AS fail  
FROM olist_orders_dataset
WHERE 1=1 
AND strftime('%Y-%m', order_purchase_timestamp) ='2017-01'
GROUP BY purchase_date
ORDER BY purchase_date