1174. Immediate Food Delivery II / LeetCode, SQL, MySQL

문제

source: LeetCode

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 an SQL query to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

 

 

 

 

제출답안(MySQL)

SELECT 
    ROUND(AVG(is_imt)*100, 2) AS immediate_percentage 
FROM 
    (SELECT 
            CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END AS is_imt
    FROM
        delivery
    WHERE
        (customer_id, order_date ) IN (SELECT customer_id, MIN(order_date) AS first_order
                                        FROM delivery
                                        GROUP BY customer_id)) AS ords_1st

 

 

문제 바로가기(MySQL)