题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

select city,driver_id,avg_grade,avg_order_num,avg_mileage from
(select *,dense_rank() over(partition by city order by avg_order_num desc) rank1 from
(select city,driver_id,round(avg(grade),1) avg_grade,
round(count(*)/count(distinct date(order_time)),1) avg_order_num,
round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage
from tb_get_car_order t1,tb_get_car_record t2
where t1.order_id=t2.order_id
group by city,driver_id
)a
)b
where rank1=1
order by avg_order_num