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

SELECT

a.video_id,

avg( CASE WHEN timestampdiff( SECOND, start_time, end_time ) >= b.duration THEN 1 ELSE 0 END ) counts

FROM

tb_user_video_log a

JOIN tb_video_info b ON a.video_id = b.video_id

GROUP BY

a.video_id