题解 | #牛客直播各科目出勤率#
牛客直播各科目出勤率
https://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899
新构建两张表分别用来计算出勤人数和报名人数,再用course_id连接
select t2.course_id,t3.course_name,round(cqs/bms*100,2)as attend_rate from ( select course_id,count(distinct user_id)as cqs from( select course_id,user_id,sum(timestampdiff(minute,in_datetime,out_datetime))as sc from attend_tb group by course_id,user_id having sc>=10 )t1 group by course_id )t2 #计算出勤人数 left join( select b.course_id,course_name,sum(if_sign)as bms from behavior_tb b left join course_tb c on b.course_id=c.course_id group by b.course_id,course_name )t3 #计算报名数 on t2.course_id=t3.course_id group by t2.course_id,t3.course_name order by course_id