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

1. 统计一下每个course_id的观看次数及总观看时长用来计算平均观看时长

 select course_id, 
        round(sum(timestampdiff(minute,in_datetime,out_datetime))/ count(course_id),2) as avg_len
        from attend_tb
        group by course_id

2. 将这个表单和course_tb合并获取course_name并按照avg_len降序

select course_name, avg_len 
    from (
        select course_id, 
        round(sum(timestampdiff(minute,in_datetime,out_datetime))/ count(course_id),2) as avg_len
        from attend_tb
        group by course_id
    ) temp 
    inner join course_tb 
    using(course_id)
    order by avg_len desc