题解 | #各个视频的平均完播率#

with
    tt1 as (
        select
            t1.video_id,
            t1.start_time,
            t1.end_time,
            t2.duration,
            t2.release_time,
            if (
                unix_timestamp (end_time) - unix_timestamp (start_time) >= t2.duration,
                1,
                0
            ) as wanbo
        from
            tb_user_video_log t1
            join tb_video_info t2 on t1.video_id = t2.video_id
        where
            start_time between '2021-01-01 00:00:00' and '2021-12-31 23:59:59'
    )
select
    video_id,
    round(sum(wanbo) / count(1), 3) as avg_comp_play_rate
from
    tt1
group by
    video_id
order by
    avg_comp_play_rate desc;