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

WITH grouped_dt as(
SELECT  city
    ,event_time uv_time
    ,1 diff
FROM tb_get_car_record
UNION ALL
SELECT city
    ,end_time
    ,-1
FROM tb_get_car_record
WHERE order_id is null
UNION ALL
SELECT city
    ,finish_time
    ,-1
FROM tb_get_car_order
LEFT JOIN tb_get_car_record
ON tb_get_car_order.order_id = tb_get_car_record.order_id
WHERE order_time is not null
AND start_time is null
UNION ALL
SELECT city
    ,start_time
    ,-1
FROM tb_get_car_order
LEFT JOIN tb_get_car_record
ON tb_get_car_order.order_id = tb_get_car_record.order_id
WHERE order_time is not null
AND start_time is not null
),
sum_dt as(
SELECT city
    ,sum(diff)over(partition by city order by uv_time,diff desc) sum_uv
FROM grouped_dt
WHERE date_format(uv_time,'%Y%m')='202110'
)
SELECT city
    ,max(sum_uv) max_wait_uv
FROM sum_dt 
GROUP BY city
ORDER BY max_wait_uv,city