题解 | 2021年国庆在北京接单3次及以上的司机统计信息
2021年国庆在北京接单3次及以上的司机统计信息
https://www.nowcoder.com/practice/992783fd80f746d49e790d33ee537c19
【1】请统计2021年国庆7天期间在北京市接单至少3次的司机的接单数和收入
select city,driver_id,count(tgco.order_id) order_cnt,sum(fare) sum_fare#,date(order_time) dt from tb_get_car_order tgco left join tb_get_car_record using(order_id) where city = '北京' and order_time is not null and date(order_time) between '2021-10-01' and '2021-10-07' group by driver_id having order_cnt >= 3易错:接单数为`order_time is not null`之前写成`start_time is not null`(有接单但是并没有送,可能用户取消了)
【2】求平均
select city,round(sum(order_cnt)/count(driver_id),3) avg_order_num, round(sum(sum_fare)/count(driver_id),3) avg_income from( select city,driver_id,count(tgco.order_id) order_cnt,sum(fare) sum_fare#,date(order_time) dt from tb_get_car_order tgco left join tb_get_car_record using(order_id) where city = '北京' and order_time is not null and date(order_time) between '2021-10-01' and '2021-10-07' group by driver_id having order_cnt >= 3 ) t1 group by city