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

select 
    city 
    ,max(wait_diff) max_wait_uv
from
(
select 
    city
    ,date(dt) day1
    ,dt
    ,sum(diff)over(partition by city,date(dt) order by dt,diff desc) wait_diff
from
(
select 
    city 
    ,event_time dt
    ,1 diff
from tb_get_car_record 
union all 
select 
    city
    ,end_time dt 
    ,-1 diff 
from tb_get_car_record 
where order_id is null 
union all 
select 
    t2.city city 
    ,ifnull(start_time,finish_time) dt
    ,-1 diff
from tb_get_car_order t1
left join tb_get_car_record t2 
on t1.order_id=t2.order_id and t1.uid=t2.uid 
) a0
where date(dt) between '2021-10-01' and '2021-10-31'
) a1
group by 1 
order by 2,1