6、-- 选出各城市连续完单天数最长的快车司机
select city_name,driver_id,司机连续完单天数
from
(select
city_name,
driver_id,
count(*) as 司机连续完单天数,
max(count(*)) over(partition by city_name) as 城市最长连续完单天数
from
(select
*,
row_number() over(partition by city_name,driver_id order by 完单日期) as 序号,
date_sub(完单日期,interval row_number() over(partition by city_name,driver_id order by 完单日期) day) as 新日期
from
(select distinct city_name,driver_id,date(finish_time) as 完单日期
from dw_v_order_base
where product_id in (3,4) and order_status=5) as t1) as t2
group by city_name,driver_id,新日期) as t3
where 司机连续完单天数=城市最长连续完单天数;