题解 | #牛客直播各科目出勤率#
牛客直播各科目出勤率
http://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899
#1、观看时间大于10分钟
with a as
(select x.course_id,course_name,
count(distinct user_id) as count_id
from attend_tb x
join behavior_tb y using (user_id,course_id)
join course_tb z on x.course_id=z.course_id
where timestampdiff(minute,in_datetime,out_datetime)>=10
group by 1,2
order by 1),
#2、报名人数
b as
(select course_id,sum(if_sign) as sum_sign from behavior_tb
group by 1 )
select a.course_id,a.course_name,
round(100*a.count_id/b.sum_sign,2)
from a join b using(course_id)
order by 1
