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

# t1加载基本信息和报名人数
with t1 as 
(select b.course_id,
c.course_name,
sum(if_sign) as sign_cnt
from behavior_tb b 
left join course_tb c
on b.course_id = c.course_id
group by b.course_id,c.course_name),
# t2用group by user_id,course_id后接着用sum保留可能出现直播间两次的总时长大于4，不想再嵌套count函数，直接用窗口函数解决
t2 as (
    select
    distinct 
    # user_id, 
    course_id,
    count(user_id) over (partition by course_id)  as attend_sum 
    from attend_tb
    group by user_id,course_id
    having sum(timestampdiff(minute,in_datetime,out_datetime))>=10
    )

select t1.course_id,
t1.course_name,
round(t2.attend_sum*100/t1.sign_cnt,2) as 'attend_rate(%)'
from t1
join t2
on t1.course_id = t2.course_id