[LeetCode/SQL] 1174. Immediate Food Delivery II

[LeetCode/SQL]

---

📌 1174. Immediate Food Delivery II

문제

풀이

- 고객의 선호 배송일이 주문 날짜와 동일한 경우, 
주문은 'immediate'로 표시되며, 그렇지 않으면 'scheduled'으로 표시된다.


- 모든 고객의 첫 번째 주문 중 'immediate' 주문의 백분율을 소수점 둘째 자리까지 구하라 

방법 I

WITH cte AS (
    SELECT 
        *,
        MIN(order_date) OVER (PARTITION BY customer_id) AS first_date,
        CASE WHEN order_date = customer_pref_delivery_date THEN 'immediate' ELSE 'scheduled' END AS cl,
        CASE WHEN order_date = MIN(order_date) OVER (PARTITION BY customer_id) THEN 'Y' ELSE 'N' END AS yn
    FROM Delivery
)

 SELECT ROUND(100* SUM(CASE WHEN cl='immediate' THEN 1 ELSE 0 END)/COUNT(*) ,2) AS immediate_percentage
 from cte
 WHERE first_date=order_date

방법 II

SELECT ROUND(AVG(order_date=customer_pref_delivery_date)*100,2) as immediate_percentage
FROM Delivery
WHERE (customer_id,order_date) 
    IN (SELECT customer_id
              ,MIN(order_date) AS first_date
        FROM Delivery
        GROUP BY customer_id
    )