题解 | #各城市最大同时等车人数#

with t1 as (
select t1.uid,city,event_time,end_time,order_time,start_time,finish_time
from tb_get_car_record t1 left join tb_get_car_order t2 
on t1.uid=t2.uid and t1.order_id=t2.order_id 
where date(event_time) between '2021-10-01' and '2021-10-31'),

t2 as(
select city, event_time as dt,1 as num from t1 
union all
select city,ifnull( start_time,end_time) as dt,-1 as num from t1
order by dt),

t3 as(
select *,sum(num) over(partition by city order by dt,num desc) as person from t2)


select city,max(person) as max_wait_uv
from t3 group by city
order by max_wait_uv