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

select 
    city,
    max(num) as max_wait_uv
from(    
    select 
        city,
        sum(uv) over (partition by city,date(dt) ORDER BY dt asc, uv DESC) as num
    from(
        select 
            city,
            event_time as dt,
            order_id,
            1 as uv
        from 
            tb_get_car_record
        where 
            left(event_time,7) = '2021-10'
        union all
        select
            city,
            start_time as dt,
            order_id,
            -1 as uv
        from 
            tb_get_car_order as a
        left join tb_get_car_record as b
        using(order_id)
        where 
            left(start_time,7) = '2021-10'
        union all
        select
            city,
            finish_time as dt,
            order_id,
            -1 as uv
        from 
            tb_get_car_order as a
        left join tb_get_car_record as b
        using(order_id)
        where 
            left(finish_time,7) = '2021-10') as t) as t1
group by 1
order by max_wait_uv asc,city asc