버뮤다 삼각지대에 들어가버린 택배
https://solvesql.com/problems/shipment-in-bermuda/
SELECT
date_format(order_delivered_carrier_date, "%Y-%m-%d") AS delivered_carrier_date,
sum(
CASE
WHEN order_delivered_customer_date is null THEN 1
ELSE 0
END
) AS orders
FROM
olist_orders_dataset
WHERE
date_format(order_delivered_carrier_date, "%y-%m") = "17-01"
AND order_delivered_customer_date is null
GROUP BY
delivered_carrier_date
ORDER BY
delivered_carrier_date지역별 자전거 대여 현황
https://solvesql.com/problems/bike-rent-stats/
SELECT
A.local,
count(*) all_rent,
sum(
CASE
WHEN A.local = C.local THEN 1
END
) same_local,
sum(
CASE
WHEN A.local != C.local THEN 1
END
) diff_local
FROM
station A
join rental_history B ON A.station_id = B.rent_station_id
JOIN station C ON C.station_id = B.return_station_id
WHERE
date_format(rent_at, "%y-%m") = '21-01'
AND date_format(return_at, "%y-%m") = '21-01'
GROUP BY
A.local
ORDER BY
all_rent DESC