[SQLite] solvesql 문제풀이 : date(), time(), datetime(), 배송 누락 택배건 조회

https://solvesql.com/

버뮤다 삼각지대에 들어가버린 택배 https://solvesql.com/problems/shipment-in-bermuda/

solvesql

 

 

조건1) 2017년 1월 한 달 동안

조건2) 배송완료는 되지 않은 = order_delivered_customer_date IS NULL

조건3) 택배사 도착 날짜 형식 = date()

 

 

---

 

 

SELECT date(order_delivered_carrier_date) as delivered_carrier_date
		,count(order_delivered_carrier_date) as orders
FROM olist_orders_dataset
WHERE order_delivered_carrier_date BETWEEN '2017-01-01' AND '2017-02-01'
		AND order_delivered_customer_date IS NULL
GROUP BY date(order_delivered_carrier_date)
ORDER BY order_delivered_carrier_date

 

date()

time()

datetime()