牛客sql题目总结（1）

1.第N高的薪水

AC:

create function getnthhighestsalary(n int) returns int
begindeclare m int; set m = n - 1; return (select distinct salaryfrom employeeorder by salary desclimit m, 1);
end

2.平均播放进度大于60%的视频类别

AC：

select tb_video_info.tag,concat(round(avg(if(timestampdiff(second, start_time, end_time) >= tb_video_info.duration, 1, timestampdiff(second, start_time, end_time) / tb_video_info.duration) * 100, 2), '%') as avg_play_progress
from tb_user_video_log
join tb_video_info on tb_video_info.video_id = tb_user_video_log.video_id
group by tb_video_info.tag
having avg(if(timestampdiff(second, start_time, end_time) >= tb_video_info.duration, 1, timestampdiff(second, start_time, end_time) / tb_video_info.duration) * 100) > 60
order by avg_play_progress desc;

解释：

• TIMESTAMPDIFF(SECOND, start_time, end_time)：计算 start_time 和 end_time 之间的秒数差异，作为播放时长。
• 通过该差异，你可以比较每个用户的观看时长与视频时长，进而计算播放进度。

这样可以确保你正确处理了时间差，并按照实际播放时长来计算进度。

3.每类视频近一个月的转发量/率

AC:

SELECT b.tag, SUM(if_retweet) retweet_cnt, ROUND(SUM(if_retweet)/COUNT(*), 3) retweet_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29
GROUP BY b.tag
ORDER BY retweet_rate desc

4.

5.

6.