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

with t1 as (
    select 
        video_info.tag,
        duration,
        case 
            when timestampdiff(SECOND,user_info.start_time, user_info.end_time) > duration
            then 100
        else timestampdiff(SECOND,user_info.start_time, user_info.end_time)/duration*100
        end as play_progress
    from tb_user_video_log as user_info
    left join(
        select 
            video_id,
            tag,
            duration
        from tb_video_info
    ) as video_info 
    on user_info.video_id = video_info.video_id
), t2 as (
    select
        tag,
        round(avg(play_progress), 2) as avg_play_progress
    from t1
    group by tag
)
select 
    tag,
    concat(avg_play_progress, '%')
from t2
where avg_play_progress > 60
order by avg_play_progress desc;