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

with cte1 as (
select 
	course_id
	,course_name 
	,substring_index(course_datetime,'-',3) as start_datetime
	,concat_ws(' ',substring_index(course_datetime,' ',1),substring_index(course_datetime,'-',-1)) as end_datetime
from course_tb
)
,
cte2 as (
select 
t1.user_id ,t1.course_id ,t3.course_name
,if(sum(TIMESTAMPDIFF(minute,in_datetime,out_datetime)) >= 10,1,0) time_
from attend_tb t1
left join behavior_tb t2
on t1.course_id = t2.course_id 
and t1.user_id = t2.user_id
LEFT JOIN cte1 t3 ON t1.course_id = t3.course_id
where if_sign = 1 and  TIMESTAMPDIFF(minute,in_datetime,out_datetime) > 0
and out_datetime >= start_datetime
and in_datetime <= end_datetime
group by t1.user_id ,t1.course_id ,t3.course_name
)
,
cte3 as (
select 
t1.course_id  ,
t3.course_name,
count(distinct t1.user_id) cn_sign
from  behavior_tb t1
left join attend_tb t2
on t1.course_id = t2.course_id 
and t1.user_id = t2.user_id
LEFT JOIN cte1 t3 ON t1.course_id = t3.course_id
where if_sign = 1
group by t1.course_id 
,t3.course_name
)
,
cte4 as(
select course_id,course_name,count(course_id) cn_up
from cte2
where time_ = 1
group by course_id,course_name
)
select
t1.course_id,t1.course_name,round(cn_up/cn_sign*100,2)
from cte4 as t1
left join cte3 as t2
on t1.course_id = t2.course_id
order by t1.course_id
;

这题有几点需要考虑的：1、报名的用户可能都没有参加上课，那么主表应该是报名表，2、一个用户可能会多次登录上课，并且有可能会出现多段少于十分钟的学习，那么要考虑将这些时间累加，最后判断累计时间是否超过10分钟，3、对于单用户单课程的多次观看或者单用户的多次报名，记得要去重。