题解 | #牛客直播各科目出勤率#
牛客直播各科目出勤率
http://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899
两张表分别计算每门课程的出勤人数和报名人数,再进行三表连接
select course_id,course_name,round(100*attend_num/sign_num,2) as 'attend_rate(%)'
from
(select course_id,count(distinct user_id) as attend_num
from attend_tb where timestampdiff(minute,in_datetime,out_datetime)>=10
group by course_id)t1
join
(select course_id,sum(if_sign) as sign_num
from behavior_tb group by course_id)t2
using(course_id)
join course_tb
using(course_id)
order by course_id