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

select tag, avg_play_progress
from (
    select tag, CONCAT(FORMAT(AVG(play_progress)*100, 2),'%') as avg_play_progress
    from (
        select uid, tag, 
        (case when watch_time >= duration then 1
        else watch_time/duration end) as play_progress
        from (
            select uid, a.video_id,
            TIMESTAMPDIFF(SECOND, start_time, end_time) as watch_time, tag, duration
            from tb_user_video_log as a
            left join tb_video_info as b
            on a.video_id = b.video_id
        ) as c
    ) as d
    group by tag
    order by avg_play_progress desc
) as e
where CAST(SUBSTRING(avg_play_progress, 1, LENGTH(avg_play_progress)-1) AS DECIMAL(5,2)) > 60.00;