题解 | #2021年国庆在北京接单3次及以上的司机统计#
2021年国庆在北京接单3次及以上的司机统计信息
https://www.nowcoder.com/practice/992783fd80f746d49e790d33ee537c19
select C.city, round(avg(drive_tm),3) as avg_order_num, round(avg(fee),3) as avg_income from (select city, driver_id, count(order_time) as drive_tm, sum(fare)as fee from tb_get_car_order left join tb_get_car_record on tb_get_car_order. order_id = tb_get_car_record.order_id where city = "北京" and order_time is not null and order_time >="2021-10-1" and order_time < "2021-10-8" group by driver_id having count(order_time) >=3) as C;
Step 1: 筛选出接单3次及其以上的司机的总收入和总接单量
筛选条件:城市是北京、接单时间在国庆、接3单及其以上
Step 2: 在此表基础上计算接单平均值和金额平均值
注意:此处使用order_time 字段而非 start_time 字段
#sql#