hello-面试题

求大家指教，我的思路是找到tph最大的那个月取数据，感觉很乱且麻烦

	select a.month
	,sum(diver_cnt) as diver_cnt
	,sum(dur_online) as dur_online
	from(
	  select diver_id, 
	  ,substr(arr_time,1,7) as month
	  ,count(diver_id) as diver_cnt
	  from order_table
	  group by diver_id
	  ,arr_time
	)a
	left join(
	  select diver_id
	  ,substr(date,1,7) as month
	  ,sum(dur_online) as dur_online
	  from online_table
	  group by diver_id
	  ,date
	)b on a.diver_id = b.diver_id and b.month = a.month
	where a.month in (
	select month
	from (
	select b.month
	,driver_id
	,row_number() over(
	  order by arr_cnt/dur_online desc
	 )rk
	from 
	(--新司机&2020&beijing
	 select diver_id
	 from driver_table
	 where substr(first_arr_time,1,4) = '2020'
	 and city_name = '北京'
	) a
	left join(
	  select diver_id, 
	  ,substr(arr_time,1,7) as month
	  ,count(order_id) as arr_cnt
	  from order_table
	  group by diver_id
	  ,arr_time
	)b on a.diver_id = b.diver_id
	left join(
	  select diver_id
	  ,substr(date,1,7) as month
	  ,sum(dur_online) as dur_online
	  from online_table
	  group by diver_id
	  ,date
	)c on a.diver_id = c.diver_id and b.month = c.month
	)where rk = 1)

下面大佬写的更清晰！

修改一下代码：

select month
,count(driver_id) as driver_cnt
,sum(dur_online) as dur_online
	from (
	  select b.month
	  ,driver_id
	  ,sum(arr_cnt) as arr_cnt
	  ,sum(dur_online) as dur_online
	  ,row_number() over(
		order by arr_cnt/dur_online desc
	  )rk
	  from 
	  (--新司机&2020&beijing
	   select diver_id
	   from driver_table
	   where substr(first_arr_time,1,4) = '2020'
	   and city_name = '北京'
	  ) a
	  left join(
		select diver_id, 
		,substr(arr_time,1,7) as month
		,count(order_id) as arr_cnt
		from order_table
		group by diver_id
		,arr_time
	  )b on a.diver_id = b.diver_id
	  left join(
		select diver_id
		,substr(date,1,7) as month
		,sum(dur_online) as dur_online
		from online_table
		group by diver_id
		,date
		)c on a.diver_id = c.diver_id and b.month = c.month
)where rk = 1
group by month