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

select tag
      ,concat(round(avg(progress)*100,2),'%') as avg_play_progress
from(
	select
    	a.video_id
       ,b.tag as tag
       ,(case when timestampdiff(second,a.start_time,a.end_time)>=b.duration then 1
         else timestampdiff(second,a.start_time,a.end_time)/b.duration*1 end) as progress
	from tb_user_video_log as a inner join tb_video_info as b 
	on a.video_id = b.video_id) tt
group by tag
having avg(progress)>0.6
order by avg(progress) desc