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

with t as (
    SELECT 
      t1.video_id,
      CASE WHEN 
	     #这两种都可以作为完播率判断
         # end_time-start_time >= duration
        timestampdiff (second,start_time,end_time) >= duration
      THEN 1 ELSE 0 END as flag
    FROM tb_user_video_log t1
    LEFT JOIN tb_video_info t2
    ON t1.video_id = t2.video_id
    WHERE YEAR(t1.start_time) = 2021
) 
SELECT 
    video_id, ROUND(avg(flag), 3) as avg_comp_play_rate
from t
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC