【牛客-SQL大厂面试真题】SQL156 各个视频的平均完播率、SQL 157 平均播放进度大于60%的视频类别

---

SQL题解汇总

https://blog.csdn.net/guliguliguliguli/article/details/126109166

01 某音短视频

SQL 156 各个视频的平均完播率

题目链接

牛客 SQL篇 SQL156 各个视频的平均完播率

答案

SELECT
	t1.video_id,
	ROUND( AVG( IF ( TIMESTAMPDIFF( SECOND, start_time, end_time )- duration >= 0, 1, 0 )), 3 ) avg_comp_play_rate 
FROM
	tb_user_video_log t1
	JOIN tb_video_info t2 ON t1.video_id = t2.video_id 
WHERE
	YEAR ( start_time ) = 2021 
GROUP BY
	video_id 
ORDER BY
	avg_comp_play_rate DESC;

SQL 157 平均播放进度大于60%的视频类别

题目连接

牛客 SQL篇 SQL 157 平均播放进度大于60%的视频类别

答案

SELECT 
	tag, CONCAT(tmp,'%') avg_play_progress
FROM ( 
	SELECT 
		tag, ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)-duration>=0,1,(TIMESTAMPDIFF(SECOND,start_time,end_time))/duration))*100,2) tmp
	FROM 
		tb_user_video_log t1 
		JOIN tb_video_info t2 ON t1.video_id = t2.video_id
	GROUP BY 
		tag
	ORDER BY 
		tmp DESC) t
WHERE 
	tmp > 60;