select t6.course_id,t6.course_name,max(sum_diff) from (
select t4.course_id,t5.course_name 
,sum(diff) over(partition by t4.course_id order by t4.dt asc,t4.diff desc) as sum_diff 
from (
select * from (
select t1.user_id,t1.course_id,t1.in_datetime as dt,1  as diff 
from attend_tb as t1 
union all 
select t2.user_id,t2.course_id,t2.out_datetime as dt ,-1 as diff 
from attend_tb as t2 ) t3 ) t4 
left join course_tb as t5 on t4.course_id = t5.course_id) t6 
group by t6.course_id,t6.course_name 
order by t6.course_id