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

select zb1.course_id,zb1.course_name,round(attend_num*100/sign_num,2) 'attend_rate(%)'
from 
(select ct.course_id,course_name,count(distinct user_id) attend_num
from course_tb ct 
left join attend_tb at on ct.course_id=at.course_id
where date(course_datetime)=date(in_datetime) and timestampdiff(minute,in_datetime,out_datetime) >= 10
group by ct.course_id,course_name) zb1 join /*先聚合再两两相连*/
(select ct.course_id,course_name,sum(if_sign) sign_num
from course_tb ct 
left join behavior_tb bt on ct.course_id=bt.course_id
group by course_id,course_name) zb2 on zb1.course_id=zb2.course_id
order by zb1.course_id