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

--外层表修改格式，添加“%”
select tag, concat(avg_play_progress, '%') as avg_play_progress
from (
	--中间表计算
    select tag, 
    round(avg(case 
    when TIMESTAMPDIFF(SECOND, start_time, end_time) <= duration
    then TIMESTAMPDIFF(SECOND, start_time, end_time) / duration
    else 1 end) * 100, 2) as avg_play_progress
    from (
		--提取原始表
        select a.start_time, a.end_time, b.duration, b.tag
        from tb_user_video_log a
        left join tb_video_info b on a.video_id = b.video_id
    ) as t1
    group by tag
    having avg_play_progress > 60
    order by avg_play_progress desc
) as t2