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

select tag, concat(avg_play_progress, "%") 
from (
select b.tag,
round(avg(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) * 100, 2) as avg_play_progress
from tb_user_video_log a
join tb_video_info b
on a.video_id = b.video_id
group by a.video_id
) t
where avg_play_progress > 60
order by avg_play_progress desc