校招算法笔面试 | SQL笔试面试编程题-被重复观看次数最多的3个视频

最新推荐文章于 2025-12-20 17:21:30 发布

原创最新推荐文章于 2025-12-20 17:21:30 发布 · 817 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#算法 #面试 #sql #算法SQL #算法SQL面试

题目## 题目

题目链接

这道题目要求我们找出那些用户重复观看次数较高的视频，我们要做的事情如下：

1. 确定总体问题

我们需要找出被重复观看次数排名前三的视频，输出这些视频的课程ID、被重复观看次数和排名。

2. 分析关键问题

连接表：将course_info_tb和play_record_tb表连接起来，以便获取每个视频的观看记录。
计算重复观看次数：对每个视频的观看记录进行计数。
计算排名：根据重复观看次数和发布日期计算排名。
筛选和排序：筛选出重复观看次数大于1且排名在前三的视频。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将course_info_tb和play_record_tb表连接起来：

from
    course_info_tb c
    join play_record_tb p on c.cid = p.cid

JOIN play_record_tb p ON c.cid = p.cid：通过课程ID连接两个表，以便获取每个视频的观看记录。

步骤2：计算重复观看次数

我们使用COUNT函数对每个视频的观看记录进行计数，并使用ROUND函数格式化输出：

select
    p.cid,
    round(count(*) * 1.0, 3) as pv

COUNT(*)：计算每个视频的观看记录数。
ROUND(count(*) * 1.0, 3)：将观看次数转换为浮点数并保留三位小数。

步骤3：计算排名

我们使用ROW_NUMBER窗口函数根据重复观看次数和发布日期计算排名：

row_number() over (
    order by
        count(*) desc,
        c.release_date desc
) as rk

ORDER BY count(*) DESC, c.release_date DESC：按观看次数降序和发布日期降序排序。
ROW_NUMBER() OVER (...)：为每个视频分配一个排名。

步骤4：筛选和排序

我们使用WHERE子句筛选出重复观看次数大于1且排名在前三的视频，并按排名升序排序：

where
    sub.pv > 1 and sub.rk <= 3
order by
    rk asc

WHERE sub.pv > 1 AND sub.rk <= 3：筛选出符合条件的视频。
ORDER BY rk ASC：按排名升序排序。

完整代码

select
    sub.cid,
    sub.pv,
    sub.rk
from
    (
        select
            p.cid,
            round(count(*) * 1.0, 3) as pv,
            row_number () over (
                order by
                    count(*) desc,
                    c.release_date desc
            ) as rk
        from
            course_info_tb c
            join play_record_tb p on c.cid = p.cid
        group by
            p.cid,
            p.uid,
            c.release_date
    ) sub
where
    sub.pv > 1 and sub.rk <=3
order by
    rk asc;

题目链接

题目描述

我们有两个表：

user：包含用户信息，包括用户ID id 和用户名 name。
grade_info：包含积分信息，包括用户ID user_id、积分数量 grade_num 和积分类型 type。

目标是查询出积分增加最高的用户的ID、名字及其总积分，并按用户ID升序排列。

知识点

窗口函数：使用SUM和DENSE_RANK窗口函数计算每个用户的总积分和排名。
连接操作：使用JOIN连接用户表和积分表。
排序：使用ORDER BY子句按用户ID升序排列结果。

关键问题分析

1. 计算每个用户的总积分和排名

我们使用SUM窗口函数计算每个用户的总积分，并使用DENSE_RANK函数按总积分降序排名：

select user_id, sum(grade_num) as grade_num,
       dense_rank() over(order by sum(grade_num) desc) as rk
from grade_info
where type = 'add'
group by user_id

sum(grade_num) as grade_num: 计算每个用户的总积分。
dense_rank() over(order by sum(grade_num) desc) as rk: 按总积分降序排名。
where type = 'add'：确定积分是增加而不是减少。
group by user_id：按照用户id分组。

2. 连接用户信息

我们使用JOIN连接用户表和积分表，以获取用户名：

join user on user.id = sub1.user_id

join user on user.id = sub1.user_id: 连接用户信息。

3. 筛选积分最高的用户

我们通过WHERE子句筛选出积分最高的用户：

where rk = 1

where rk = 1: 筛选出积分最高的用户。

4. 排序输出

我们按用户ID升序排列输出结果：

order by user.id asc

order by user.id asc: 按用户ID升序排列。

完整代码

select user.id, user.name, sub1.grade_num
from (
    select user_id, sum(grade_num) as grade_num,
           dense_rank() over(order by sum(grade_num) desc) as rk
    from grade_info
    where type = 'add'
    group by user_id
) sub1
join user on user.id = sub1.user_id
where rk = 1
order by user.id asc;