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