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

with t1 as (
        select
            a.video_id,
            a.tag,
            a.duration as video_long,
            timestampdiff (second, b.start_time, b.end_time) as watch_long
        from
            tb_video_info a join tb_user_video_log b on a.video_id = b.video_id
    ),

t2 as  (select tag,
                if (t1.watch_long>=t1.video_long,100,watch_long/video_long*100) as each_video_progress
         from t1),
 t3 as (select
       t2.tag,
       sum(each_video_progress)/count(tag) as avg_play_progress_1
       from t2 group by tag)
select tag,
       concat(round(avg_play_progress_1,2),"%") as avg_play_progress
       from t3 where avg_play_progress_1>60 ORDER BY avg_play_progress_1 Desc;