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

SELECT
    city,MAX(uv) AS max_wait_uv
FROM
(
    SELECT
        city
        ,event_time
        ,COUNT(DISTINCT uid) AS uv
    FROM
    (
        SELECT
            t1.uid
            ,t1.city
            ,t1.event_time AS st 
            ,CASE WHEN t1.order_id=null THEN t1.end_time
                WHEN t2.start_time=null THEN t2.finish_time
                ELSE start_time END AS et
        FROM
            tb_get_car_record AS t1
        LEFT JOIN
            tb_get_car_order AS t2
        ON t1.order_id=t2.order_id
    )
    AS t1
    RIGHT JOIN
    (
        SELECT
        DISTINCT
        event_time
        FROM tb_get_car_record
    ) AS t2
    ON st<=event_time AND et>=event_time
    GROUP BY
        city
        ,event_time
    HAVING left(event_time,7)='2021-10'
) AS t1
GROUP BY
    city
ORDER BY
    max_wait_uv,city