原题参考自:SQL 大厂面试真题篇_sql大厂面试题_withoutfear的博客-CSDN博客
SQL1 各个视频的平均完播率
描述: 用户-视频互动表tb_user_video_log
id | uid | video_id | start_time | end_time | if_follow | if_like | if_retweet | comment_id |
1 | 101 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:30 | 0 | 1 | 1 | NULL |
2 | 102 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:24 | 0 | 0 | 1 | NULL |
3 | 103 | 2001 | 2021-10-01 11:00:00 | 2021-10-01 11:00:34 | 0 | 1 | 0 | 1732526 |
4 | 101 | 2002 | 2021-09-01 10:00:00 | 2021-9-01 10:00:42 | 1 | 0 | 1 | NULL |
5 | 102 | 2002 | 2021-10-01 11:00:00 | 2021-10-01 10:00:30 | 1 | 0 | 1 | NULL |
(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)
短视频信息表tb_video_info
id | video_id | author | tag | duration | release_time |
1 | 2001 | 901 | 影视 | 30 | 2021-01-01 07:00:00 |
2 | 2002 | 901 | 美食 | 60 | 2021-01-01 07:00:00 |
3 | 2003 | 902 | 旅游 | 90 | 2021-01-01 07:00:00 |
(video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长(秒), release_time-发布时间)
问题:计算2021年里有播放记录的每个视频的完播率(结果保留三位小数),并按完播率降序排序
注:视频完播率是指完成播放次数占总播放次数的比例。简单起见,结束观看时间与开始播放时间的差>=视频时长时,视为完成播放。
本人写的:
select
video_id,
round(sum(com_stat)/count(video_id),3) as com_ro
from
(
select
aa.video_id,
start_time,
end_time,
timestampdiff(second,end_time,start_time) as play_time,
case when play_time>=bb.duration then 1 else 0 end as com_stat
from
tb_user_video_log aa
left join
tb_video_info bb
on
aa.video_id=bb.video_id
where
year(start_time)=2021
)
group by video_id
order by com_ro desc;
chatGPT 3.5:
SELECT
l.video_id,
ROUND(SUM(CASE WHEN TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= v.duration THEN 1 ELSE 0 END) / COUNT(*), 3) AS completion_rate
FROM
tb_user_video_log l
JOIN
tb_video_info v
ON
l.video_id = v.video_id
WHERE
YEAR(l.start_time) = 2021
GROUP BY l.video_id
ORDER BY completion_rate DESC;
问题:计算各类视频的平均播放进度,将进度大于60%的类别输出。
注:播放进度=播放时长÷视频时长*100%,当播放时长大于视频时长时,播放进度均记为100%。
结果保留两位小数,并按播放进度倒序排序。
本人写的:
select
tag,
round(avg(play_per),2) as avg_play_per
from
(
select
aa.video_id,
aa.start_time,
aa.end_time,
timestampdiff(second,aa.end_time,aa.start_time) as play_time,
if(play_time>bb.duration,1,play_time/bb.duration)*100 as play_per
bb.tag
from
tb_user_video_log aa
left join
tb_video_info bb
on
aa.video_id=bb.video_id
)
group by tag
having avg_play_per>60
order by avg_play_per desc;
chatGPT:
SELECT
v.tag,
ROUND(SUM(CASE WHEN TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= v.duration THEN 100 ELSE TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) / v.duration * 100 END) / COUNT(*), 2) AS avg_progress
FROM
tb_user_video_log l
JOIN
tb_video_info v
ON
l.video_id = v.video_id
GROUP BY v.tag
HAVING avg_progress > 60
ORDER BY avg_progress DESC;