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

with t1 as (

    select unix_timestamp(vl.end_time) - unix_timestamp(vl.start_time) ts, vi.tag, vi.duration

    from tb_user_video_log vl join tb_video_info vi on vl.video_id = vi.video_id

), t2 as (

    select distinct tag,

        cast(sum(case when ts >= duration then duration else ts end) over(partition by tag)/sum(duration) over(partition by tag)*100 as decimal(16,2))  avg_play_progress

    from t1

), t3 as (

    select tag, concat(avg_play_progress, '%')

    from t2

    where avg_play_progress > 60

    order by avg_play_progress desc

)

select * from t3