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

WITH a AS(
SELECT city, event_time AS uv_time, 1 AS uv_cnt
FROM tb_get_car_record
UNION ALL
SELECT city, end_time AS uv_time, -1 AS uv_cnt
FROM tb_get_car_record
WHERE order_id IS NULL
UNION ALL
SELECT city, IFNULL(start_time, finish_time) AS uv_time, -1 AS uv_cnt
FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
),

b AS (SELECT city, SUM(uv_cnt) OVER (PARTITION BY city ORDER BY uv_time, uv_cnt DESC) AS sss FROM a WHERE uv_time LIKE '2021-10%')

SELECT city, MAX(sss) AS max_wait_uv FROM b
GROUP BY city 
ORDER BY max_wait_uv, city