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

with tt1 as(
select tag,concat_ws('',substr(round(sum(guankanshichang)/sum(duration),4)*100,1,5),'%') as	avg_play_progress from
    (select
            t1.start_time,
            t1.end_time,
            t2.duration,
            t2.tag ,
            case when unix_timestamp(end_time) - unix_timestamp(start_time) <= t2.duration
                     then  unix_timestamp(end_time) - unix_timestamp(start_time)
                 when  unix_timestamp(end_time) - unix_timestamp(start_time) > t2.duration
                     then t2.duration
                end as guankanshichang
     from tb_user_video_log t1
              join tb_video_info t2
                   on t1.video_id = t2.video_id) t1
group by tag
order by avg_play_progress desc
)select * from tt1 where round(substr(avg_play_progress,1,5)/100,4) > 0.6000 ;