题解 | #牛客直播各科目出勤率#

注重要的是理清逻辑

with a as(
select course_id,user_id,case when sum(TIMESTAMPDIFF(MINUTE,in_datetime,out_datetime))>=10 then 1
else 0 end as attend
from attend_tb
group by course_id,user_id)
select bt.course_id,course_name,
round(sum(attend)/sum(if_sign)*100,2) as 'attend_rate(%)'
from behavior_tb bt
left join a on bt.user_id=a.user_id and bt.course_id=a.course_id
left join course_tb ct on bt.course_id=ct.course_id
group by bt.course_id,course_name
order by bt.course_id