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

select t1.tag
    ,avg(case when TIMESTAMPDIFF(SECOND,t2.start_time,t2.end_time) < t1.duration
         then TIMESTAMPDIFF(SECOND,t2.start_time,t2.end_time)/t1.duration
        else 1
        end) as avg_play_progress
  from tb_video_info t1
  join tb_user_video_log t2 
    on t1.video_id = t2.video_id
group by t1.tag
    )
select tag,concat(cast(round(avg_play_progress*100,2) as CHAR),'%') 
from t 
where avg_play_progress>0.6
order by avg_play_progress desc