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

第一步，将三个表连接，并计算得到每门课程在线时长大于10分钟的用户数

select 
t2.course_id,t2.course_name,count(distinct t3.user_id) attend
from
behavior_tb t1
join
course_tb t2
using(course_id) 
join 
attend_tb t3
using(course_id, user_id)
where timestampdiff(minute,in_datetime,out_datetime)>=10
group by t2.course_id,t2.course_name 
#t1表

第二步，根据behavior_tb计算出参与报名的人数

select 
course_id,sum(if_sign) cnt
from behavior_tb
group by course_id 
#t2表

最后将t1表和t2表依据course_id连接，并计算round(attend/cnt*100,2)即为出勤率

最终的sql如下

with t1 as(
select 
t2.course_id,t2.course_name,count(distinct t3.user_id) attend
from
behavior_tb t1
join
course_tb t2
using(course_id) 
join 
attend_tb t3
using(course_id, user_id)
where timestampdiff(minute,in_datetime,out_datetime)>=10
group by t2.course_id,t2.course_name),
t2 as(
select 
course_id,sum(if_sign) cnt
from behavior_tb
group by course_id )

select t1.course_id, t1.course_name, round(attend/cnt*100,2) attend_rate
from t1
join t2
using(course_id)
order by course_id