[SolveSQL/난이도 3] 데이터리안 SQL 데이터 분석 캠프 실전반(MySQL, SQLite)

배송 예정일 예측 성공과 실패

SELECT DATE(order_purchase_timestamp) AS `purchase_date`
, COUNT(DISTINCT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN order_id END)) AS `success`
, COUNT(DISTINCT(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date THEN order_id END)) AS `fail`
FROM olist_orders_dataset
GROUP BY DATE(order_purchase_timestamp)
HAVING CAST(STRFTIME('%Y', DATE(order_purchase_timestamp)) AS INTEGER) = 2017 AND CAST(STRFTIME('%m', DATE(order_purchase_timestamp)) AS INTEGER) = 1
ORDER BY `purchase_date` ASC