문제

1174. Immediate Food Delivery II
If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.
The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.
Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

각 customer_id 별로 첫 번째 주문이 immediate인지 판별하여 그 비율을 구하는 문제

풀이 과정

윈도우 함수

문제를 보자마자 윈도우 함수를 써야겠다는 생각을 했다
row_number()로 partition을 customer_id로 잡고, order by order_date를 하면 정확하게 문제에서 요구하는 바를 구할 수 있다.

최종 코드

percentage를 구함에 있어서
분자: order_date = customer_pref_delivery_date인 경우, 즉 1(True)인 경우를 전부 sum()
분모: 전체 count()
으로 계산했다.

WITH first_order_per_customer AS (
    SELECT
        customer_id
        , case
            when order_date = customer_pref_delivery_date 
            then 1
            else 0
        end as "is_immediate"
        , row_number() over (
            partition by customer_id 
            order by order_date
        ) as "rn"
    FROM
        Delivery
)
SELECT
    ROUND(
        sum(is_immediate) / count(1) * 100
        , 2
    ) as "immediate_percentage"
FROM
    first_order_per_customer
WHERE 
    rn = 1
;

다른 풀이법

다른 사람들의 코드를 보다가 인상 깊었던 방식이 있어서 가져와보았다.

WHERE절 서브쿼리

나는 문제를 보자마자 윈도우 함수와 sum(True)를 떠올렸는데,
사실 보다 쉬운 함수들을 사용해서 풀 수도 있었다

SELECT
    ROUND(
        AVG(
            CASE
                WHEN order_date = customer_pref_delivery_date
                THEN 1
                ELSE 0
            END
        ) * 100
        , 2
    ) as "immediate_percentage"
FROM
    Delivery
WHERE
    (customer_id, order_date) in (
        SELECT
            customer_id
            , min(order_date)
        FROM
            Delivery
        GROUP BY
            customer_id
    )    
;

(1) WHERE절에 min(order_date) 사용
어차피 customer_id별로 첫번째 order_date인 row를 취하는 것이므로 min(order_date)를 구하는 것.
customer_id별로 구해야하므로 WHERE절에서 (customer_id, order_date)의 쌍으로 걸어주면 된다.

(2) AVG() 사용
평균의 정의는 “조건에 맞는 케이스 / 전체 경우의 수”이다.
그런데 지금 문제가 “order_date = customer_pref_delivery_date”인 경우의 수를 찾는 것이므로,
결국 AVG()에 넣어버리면 “조건에 맞는 경우의 수 / 전체 경우의 수”가 된다

SELF JOIN 사용

SELECT 
	ROUND(
		SUM(
			IF(d1.order_date = d1.customer_pref_delivery_date, 1, 0)
			)
    / COUNT(*) * 100
    , 2
   ) AS immediate_percentage
FROM 
	Delivery d1
	LEFT JOIN Delivery d2
    ON d1.customer_id = d2.customer_id 
    AND d2.order_date < d1.order_date
WHERE
	d2.order_date IS NULL
;

이 코드는 SELF JOIN을 통해 d1에 first_order를 남기는 것이 목적이다.

LEFT JOIN의 조건으로 d2.order_date < d1.order_date를 적으면 2가지 케이스만 필터링할 수 있다
(1) d1의 날짜보다 일찍 발생한 거래 케이스. 이들은 모두 찾고자하는 값이 ‘아닌’ 경우
(2) d2.order_date가 null인 경우. NULL은 사칙연산이 볼가능하기에 해당 조건으로 필터링된다.

즉 d2.order_date < d1.order_date는 일부러 “첫번째 거래 케이스(null이 나오는 경우)”와 “그 외 조건에 맞지 않는 경우”를 찾아내서, null만 남김으로써 first_order만 남기고자 한 코드이다.