题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
SELECT video_id,round(SUM(IF(duration<=dt,1,0))/COUNT(*),3) rate FROM ( SELECT log.video_id,duration,TIMESTAMPDIFF(SECOND,start_time,end_time) dt FROM tb_user_video_log log JOIN tb_video_info info ON log.video_id = info.video_id WHERE YEAR(start_time) = 2021 )t1 GROUP BY video_id ORDER BY rate DESC;
美团一面1974人在聊
