题解 | 牛客直播各科目平均观看时长

# select course_name,
#         round(avg(timestampdiff(minute,if(in_datetime<=substring(course_datetime,1,15),course_datetime,in_datetime),out_datetime)),2) as avg_Len
#     from course_tb a
#     join attend_tb b
#     on a.course_id=b.course_id
#     group by course_name
#     order by  avg_Len desc

select course_name,
        round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len
    from course_tb a
    join attend_tb b
    on a.course_id=b.course_id
    group by course_name
    order by  avg_Len desc

题干对于观看时长的定义不严谨，课程开始时间之前进入直播间的用户观看时长严格上不应该作为直播的观看时长，应该统计直播开始后至直播结束这段时间内的观看时长（注解部分代码）