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

with a as(
    select
        tvi.tag,
        if(timestampdiff(second,tuvl.start_time,tuvl.end_time)/tvi.duration>=1,1,timestampdiff(second,tuvl.start_time,tuvl.end_time)/tvi.duration) as jindu
    from 
        tb_user_video_log as tuvl 
        left join tb_video_info as tvi on tuvl.video_id = tvi.video_id
),
b as(
    select
        tag,
        concat(round(100*avg(jindu),2),'%') as avg_play_progress
    from 
        a
    group by 
        tag
    having
        avg_play_progress>'60.00%'
    order by 
        avg_play_progress desc
)
select * from b