牛客sql题目总结（1）

1.第N高的薪水

AC:

create function getnthhighestsalary(n int) returns int
begin
    declare m int; 
    set m = n - 1; 
    return (
        select distinct salary
        from employee
        order by salary desc
        limit 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.