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

with basic as
(select *
from(select uid,city,event_time as dt,1 as flag
from tb_get_car_record 

union 
select tr.uid,city,ifnull(start_time,finish_time)as dt,-1 as flag
from tb_get_car_order too
right join tb_get_car_record tr on tr.order_id = too.order_id

union
select uid,city,end_time as dt,-1 as flag
from  tb_get_car_record
where order_id is null  )a
where date_format(dt,'%Y%m')='202110')

select city,max(cnt) as max_wait_uv
from
(select city,date(dt),sum(flag) over(partition by city,date(dt) order by dt,flag desc) as cnt
from basic)b
group by city
order by max_wait_uv, city

三刷。

易错：在建立basic时不用计算order_time，因为从event_time到order_time等待的人数并没有增加；