题解 | #牛客直播各科目出勤率#
牛客直播各科目出勤率
https://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899
可能答案稍显麻烦
首先构造一个表存放每种课程每个人观看时间，其次构造一个表存放每个节目的报名人数，进行除法即可。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
with tb(course_id,course_name,user_id,minu) as(
select min(course_id),course_name,user_id,
sum(timestampdiff(minute,in_datetime,out_datetime))
from attend_tb inner join course_tb using(course_id)
group by course_name,user_id
)
select a.course_id,course_name,round(c/cc*100,2)
from(
select course_id,min(course_name) course_name,count(user_id) c
from tb
where minu>=10
group by course_id)a inner join (select course_id,count(user_id) cc
from behavior_tb
where if_sign=1
group by course_id
) b using(course_id)
|
