[LeetCode] immediate-food-delivery-ii

https://leetcode.com/problems/immediate-food-delivery-ii/?envType=study-plan-v2&envId=top-sql-50

Immediate Food Delivery II - LeetCode

내 쿼리는 다음과 같다

SELECT ROUND(COUNT(CASE WHEN imd_order = 'immediate' THEN customer_id ELSE NULL END)*100
    /COUNT(customer_id), 2) AS immediate_percentage
FROM (
SELECT customer_id,
    order_date,
    customer_pref_delivery_date,
    CASE WHEN order_date = customer_pref_delivery_date THEN 'immediate'
        ELSE 'scheduled' END AS imd_order
FROM delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id,
        MIN(order_date) AS first_order
    FROM Delivery
    GROUP BY customer_id)
) A

 

1. customer별 first_order 구하기

SELECT customer_id,
	MIN(order_date) AS first_order
FROM Delivery
GROUP BY customer_id

 

2. 1번 쿼리를 WHERE 절 서브쿼리로 사용하여 유저별 first_order의 주문일자, 수령희망일자를 뽑은 후

해당 column들을 이용해 immediate와 scheduled 구분하는 column 생성 (CASE문 이용)

SELECT customer_id,
    order_date,
    customer_pref_delivery_date,
    CASE WHEN order_date = customer_pref_delivery_date THEN 'immediate'
        ELSE 'scheduled' END AS imd_order
FROM delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id,
        MIN(order_date) AS first_order
    FROM Delivery
    GROUP BY customer_id)

 

3. 2번을 FROM절 서브쿼리에 집어넣은 후 immediate_percentage를 구하기 위한 계산식 작성

SELECT ROUND(COUNT(CASE WHEN imd_order = 'immediate' THEN customer_id ELSE NULL END)*100
    /COUNT(customer_id), 2) AS immediate_percentage
FROM (
SELECT customer_id,
    order_date,
    customer_pref_delivery_date,
    CASE WHEN order_date = customer_pref_delivery_date THEN 'immediate'
        ELSE 'scheduled' END AS imd_order
FROM delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id,
        MIN(order_date) AS first_order
    FROM Delivery
    GROUP BY customer_id)
) A

 

Solution을 보니까 더 쉽게 풀 수 있는 방법이 있더라 !

WHERE절 사용까지는 비슷한데 굳이 column을 만들지 않고 풀 수 있는 쿼리가 있다.

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_order
    FROM Delivery
    GROUP BY customer_id)

AVG 함수를 이용해서 조건을 만족하는 row 수의 평균을 구해줄 수도 있다! 우와 ~