Immediate Food Delivery II

[문제]

https://leetcode.com/problems/immediate-food-delivery-ii/description/

 

[풀이]

1. 각 고객별로 첫주문을 가져오고, 해당 주문과 희망 배송 날짜가 같은 걸 찾고 더한 뒤 전체 개수로 나눠줌

 

Pandas

import pandas as pd

def immediate_food_delivery(delivery: pd.DataFrame) -> pd.DataFrame:
    delivery = delivery.groupby('customer_id').min()
    return pd.DataFrame({'immediate_percentage' : [round((delivery['order_date'] == delivery['customer_pref_delivery_date']).mean() * 100, 2)]})

 

SQL

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

SELECT 
    ROUND(SUM(order_date=customer_pref_delivery_date AND first_order=1) / COUNT(DISTINCT customer_id) * 100, 2) AS immediate_percentage 
FROM 
(
SELECT
    *
    , RANK() OVER(PARTITION BY customer_id ORDER BY order_date) AS first_order
FROM Delivery
) a