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

with a3 as(
with a2 as(
with a1 as(
select city, event_time dt
,1 rink
from tb_get_car_record t1
join tb_get_car_order t2
on t1.order_id =t2.order_id
where event_time like '2021-10%'
union all 
(select city
,if(if(start_time is null,finish_time,start_time) is null,end_time,if(start_time is null,finish_time,start_time)) dt
,-1 rink
from tb_get_car_record t1
join tb_get_car_order t2
on t1.order_id =t2.order_id
where event_time like '2021-10%')
order by dt,rink desc)
select city,dt,rink
,row_number()over(partition by city,date(dt) order by dt) row_num
from a1)
select city
,sum(rink)over(partition by city,date(dt) order by row_num) cumsum
from a2)
select city,max(cumsum) max_wait_uv
from a3
group by city
order by max_wait_uv,city;