LeetCode-1173/1174. 即时食物配送I / II（中等）

1174. 即时食物配送II
      
      
      
      题目来源：力扣（LeetCode）
      链接：https://leetcode-cn.com/problems/immediate-food-delivery-ii
      著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

本人思路：
– 1、分子是首次订单为即时订单，即首次订单的配送日期和下单日期相同，分母为首次订单为计划订单，即首次订单的配送日期和下单日期不同
– 2、先找到每个客户首次订单的日期构成temp表与Delivery表内连接，且满足配送日期=min日期，计算这样的用户个数作为分子
– 3、从temp表中取出用户的个数作为分母

select round(
    (select count(temp.customer_id)
    from Delivery as d1 inner join(
        select customer_id, min(order_date) as min_date
        from Delivery 
        group by customer_id
         )temp
    on d1.customer_id = temp.customer_id
    where d1.customer_pref_delivery_date = temp.min_date)
    /
    (select count(customer_id)
    from (
        select customer_id, min(order_date) as min_date
        from Delivery 
        group by customer_id
        )temp)
    *100,2) as immediate_percentage

较为简洁的方法
1、注意sum()的用法，内部为True自动加1即为符合条件的分子，分母仍需要找到每个客户的min日期，计算客户数量

select round (sum(order_date = customer_pref_delivery_date) * 100 /count(*),2) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
    select customer_id, min(order_date)
    from delivery
    group by customer_id
)

1173. 即时食物配送I

题目来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/immediate-food-delivery-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

select round(sum(order_date = customer_pref_delivery_date)*100/count(delivery_id),2) as immediate_percentage 
from Delivery