2021-05-19

Immediate Food Delivery

需求一：查询语句获取即时订单所占的百分比， 保留两位小数。

展示效果：

immediate_percentage
42.86

Create table If Not Exists 56_Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table 56_Delivery;
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (1, 1, '2019-08-01', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (2, 5, '2019-08-02', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (3, 1, '2019-08-11', '2019-08-11');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (4, 3, '2019-08-24', '2019-08-26');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (5, 4, '2019-08-21', '2019-08-22');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (6, 2, '2019-08-11', '2019-08-13');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (7, 4, '2019-08-09', '2019-08-09');

最终SQL:

SELECT
	round(sum(number)/count(delivery_id)*100,2) immediate_percentage
FROM(
	SELECT
		delivery_id,
		customer_id,
		order_date,
	IF(order_date = customer_pref_delivery_date,1,0 ) number
	FROM
		56_delivery
) t1

需求二：查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

展示效果：

immediate_percentage
40.00

最终SQL:

SELECT
	round(
	(select 
	count(customer_id)
from 
	56_delivery
where 
	order_date = customer_pref_delivery_date) / 
 	(SELECT
	count(DISTINCT customer_id)
	FROM
	(select 
	customer_id,
	order_date
from 
	56_delivery
GROUP BY
	customer_id ,order_date) t1)*100,2)