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

with a as
(select course_id,course_name,
count(distinct case when timestampdiff(minute,in_datetime,out_datetime)>=10 then user_id else null end) attend_num
from attend_tb
left join course_tb using(course_id)
group by course_id,course_name),
b as 
(select course_id,sum(if_sign) as sign_num
from behavior_tb
group by course_id)
select course_id,course_name,round(100*a.attend_num/b.sign_num,2) as 'attend_rate(%)'
from a left join b using(course_id)
order by course_id

1、先求出报名人数，作为表b

表：behavior_tb

报名人数：sum(if_sign) as sign_num

按照课程ID分组：group by course_id

2、求出出勤人数，作为表a

表：attend_tb和course_tb连接，用以取出课程名称

出勤人数：count（distinct）

大于10分钟：用case when 和 timestampdiff函数，

count(distinct case when timestampdiff(minute,in_datetime,out_datetime)>=10 then user_id else null end) attend_num

按照课程ID、名称分组：group by course_id,course_name

3、连接b和a表，临时表更简单

4、求出出勤率

出勤率=出勤人数/报名人数：attend_num/ sign_num

保留2位小数：round（x,2）

5、排序

order by course_id