1174. Immediate Food Delivery II : leetcode

custoemr_id마다order_date가 가장 빠른 record만 남기려면?

• 날짜 순으로 정렬한 다음에 가장 빠른 행만 남기는 방법 (CTE)
• 위 방법을 window function으로 하기

answer

WITH sort_date AS (SELECT  customer_id, order_date,
                    customer_pref_delivery_date,
                    IF(order_date = customer_pref_delivery_date, 'immediate', 'scheduled') AS checked,
                    RANK() OVER(PARTITION BY customer_id ORDER BY order_date ASC ) as ranking
            FROM    Delivery)

SELECT  ROUND(AVG(IF(checked = 'immediate', 1, 0)) * 100, 2) as immediate_percentage
FROM    sort_date
WHERE   ranking = 1