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

with a as(
    select
        l.video_id as video_id,
        tag,
        duration,
        timestampdiff(second,start_time,end_time) as watch_time
    from tb_user_video_log l
        join tb_video_info i on l.video_id=i.video_id
),
b as (
    select
        video_id,
        tag,
        case
            when watch_time>=duration then 1
        else  watch_time/duration
        end watch
    from a
)
select
    tag,
    concat(round(avg(watch)*100,2),"%") as avg_play_progress
from b
group by tag
having round(avg(watch)*100,2)>60
order by avg_play_progress desc;