6、-- 選出各城市連續(xù)完單天數最長的快車司機
select city_name,driver_id,司機連續(xù)完單天數
from
(select
city_name,
driver_id,
count(*) as 司機連續(xù)完單天數,
max(count(*)) over(partition by city_name) as 城市最長連續(xù)完單天數
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 司機連續(xù)完單天數=城市最長連續(xù)完單天數;