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

-- 各个城市，2021年10月期间，单日最大同时等车人数
with t1 as (select city,a.uid,event_time,
case when b.order_time is null then end_time 
when a.end_time = b.order_time and b.start_time is not null then b.start_time 
when a.end_time = b.order_time and b.start_time is null then b.finish_time else null end over_time
from tb_get_car_record a 
left join tb_get_car_order b
on a.uid = b.uid
and a.order_id = b.order_id
where substr(event_time,1,7) = '2021-10'
),
t2 as (select event_time dt from t1
union all
select over_time dt from t1)

select city,max(wait_uv) max_wait_uv from (select city,dt,count(distinct case when follow_num = 1 then uid else null end) wait_uv
from (select dt,city,uid,
case when t1.event_time > dt then 0 when t1.event_time <= dt and t1.over_time >= dt then 1 when t1.over_time < dt then 0 end follow_num
from t2 join t1) u 
group by city,dt) v 
group by city
order by max_wait_uv,city ASC