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

select tbu.video_id,
        round(count(
            if(timeStampdiff(second, start_time,
                 end_time  ) >= duration, 1, null)
        ) / count(start_time),
                 3)as avg_comp_play_rate
from tb_user_video_log as tbu 
left join tb_video_info as tbv 
on tbu.video_id = tbv.video_id
where year(start_time) = 2021
group by tbu.video_id
order by avg_comp_play_rate desc