- 即时食物配送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
)
- 即时食物配送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