[SQL 알고리즘] SolvedSql - 배송 예정일 예측 성공과 실패

✅ 문제

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

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

 

✅ 코드

 

select date(a.order_purchase_timestamp) as purchase_date,
(select count(DISTINCT order_id) from olist_orders_dataset b 
where order_estimated_delivery_date  > order_delivered_customer_date  
and date(a.order_purchase_timestamp) = date(b.order_purchase_timestamp)) as success,
(select count(DISTINCT order_id) from olist_orders_dataset b 
where order_estimated_delivery_date  <= order_delivered_customer_date  
and date(a.order_purchase_timestamp) = date(b.order_purchase_timestamp)) as fail
 from olist_orders_dataset  a
 where date(a.order_purchase_timestamp)  BETWEEN "2017-01-01" and "2017-01-31"
group by date(a.order_purchase_timestamp);

 

처음에 이렇게 바로 만들긴했는데 속도도 엄청 느린거같고 비율적이라는 생각이 들어서 더 좋은방법을 고민하다가 찾은게 

CASE WHEN으로 그냥 나타내느것이었다. 그리고 조건에 구매날짜, 배송예정시간이 NULL체크까지 넣어줘서 속도가 23초정도 걸린거를 0.5초아래로 줄였다.

✅ 수정된 코드

select date(a.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  a
 where date(a.order_purchase_timestamp)  BETWEEN "2017-01-01" and "2017-01-31"
 AND a.order_estimated_delivery_date is not null
 and a.order_delivered_customer_date is not null
group by date(a.order_purchase_timestamp);

 

✏️ 공부한점

CASE WHEN 구문을 자주 사용해야될거같고 속도측면으로도 고려해야되서 서브쿼리를 자주안쓰는게 좋을꺼같다.