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

with s as(
select b.course_id,course_name,sum(if_sign) as sign_num
from behavior_tb b left join course_tb c on b.course_id=c.course_id
group by course_id,course_name
)

select a.course_id,course_name,round(at_num/sign_num*100,2) as 'attend_rate(%)'
from
(
select course_id,count(user_id) as at_num
from
(select course_id,user_id,sum(timestampdiff(minute,in_datetime,out_datetime)) as attime
from attend_tb
group by course_id,user_id
having attime>=10) att
group by course_id
)a
inner join
s b on a.course_id =b.course_id