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

WITH total AS (
SELECT
city,
event_time AS dt,
1 AS tag

FROM tb_get_car_record 
RIGHT JOIN tb_get_car_order
USING(order_id)

UNION ALL

SELECT
city,
COALESCE(start_time, finish_time) AS dt,
-1 AS tag

FROM tb_get_car_record 
RIGHT JOIN tb_get_car_order
USING(order_id))

SELECT
city,
MAX(waiting_num) AS max_wait_uv

FROM(
SELECT
city,
SUM(tag) OVER(PARTITION BY city ORDER BY dt,tag DESC) AS waiting_num

FROM total

WHERE LEFT(dt, 7) = '2021-10') AS t1

GROUP BY city

ORDER BY max_wait_uv,city