题解 | #平均播放进度大于60%的视频类别#

select 视频类别, concat(round(sum(播放进度)/count(video_id)*100,2),'%') from (select a.video_id, (case when TIMESTAMPDIFF(second,a.start_time,a.end_time) <= b.duration then TIMESTAMPDIFF(second,a.start_time,a.end_time)/b.duration else 1 end) as 播放进度, b.tag as 视频类别 from tb_user_video_log a left JOIN tb_video_info b on a.video_id = b.video_id) c group by 视频类别 having sum(播放进度)/count(video_id)*100> 60 order by sum(播放进度)/count(video_id) desc;