牛客SQL【常见面试手撕SQL】

2023-08-13 13:55:58

笔试题66：牛客每个人最近的登录日期(一)

详细描述：请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序

解题思路

按照userid进行分组，取最大的登录时间

源代码

select user_id, max(date)
from login
group by user_id
order by user_id

笔试题67：牛客每个人最近的登录日期(二)

详细描述：请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

解题思路

用户的名字在user表中，设备的名字在client表中，登录的日子在login表中
login和client依靠client_id连接，login和user依靠user_id

源代码

select u.name, c.name, date
from login l
         join user u on l.user_id = u.id
         join client c on l.client_id = c.id
where (l.user_id, l.date) in (
    select user_id, max(date)
    from login
    group by user_id
)
order by u.name;

笔试题68：牛客每个人最近的登录日期(三)【留存率】**

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

解题思路

次日留存率：（第一天登录的新用户并且第二天也登录的用户）/（总用户）
首先获取总用户

select count(distinct user_id) from login
然后找到用户第一天就登录的用户

select user_id,min(date) from login group by user_id
同时找到第二天也登录的用户，我们可以通过如下来获取

select user_id,date_add(min(date),interval 1 day) from login group by user_id

源代码

select round(count(user_id) / (select count (distinct user_id) from login), 3)
from login
where (user_id, date) in (
    select user_id, date_add(min(date), interval 1 day)
    from login
    group by user_id
);

补充知识

mysql里查找某一天的后一天的用法是: DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)

笔试题69：牛客每个人最近的登录日期(四)

详细描述：请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序

解题思路

sum+case
按照用户登录日期分组，判断当前用户登录是否为首次登录，如果是就加1
- 首次登录：select user_id, min(date) from login group by user_id

源代码

select
       date,
        sum(
        case when (user_id,date) in (select user_id, min(date)from login group by user_id)
        then 1 else 0 end)
from login
group by date
order by date;

笔试题70：牛客每个人最近的登录日期(五)

详细描述：请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序

解题思路

读完题目，我们可以将题目转换为，求 新用户表，和留存用户表
再次提醒，拆分题目要求非常关键！！！

源代码

select temp1.date, round(if(first_login = 0, 0, ifnull(retain, 0) / first_login), 3) res
from (
         -- 每天的新增用户数
         select date,
                sum(case
                        when
                                (user_id, date) in (
                                select user_id, min(date)
                                from login
                                group by user_id) then 1
                        else 0 end) first_login
         from login
         group by date
     ) temp1
    left join (
        -- 每天的留存用户数
        select date, date_sub(date, interval 1 day) new_date, count(*) retain
        from login
        where (user_id, date) in (
            select user_id, date_add(min(date), interval 1 day)
            from login
            group by user_id
        )
        group by date
    ) temp2 on temp1.date = temp2.new_date
order by date

笔试题71：牛客每个人最近的登录日期(六)

详细描述：请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出

解题思路

这个题目只需要注意到两点：
- 如何删除登录却没有刷题的数据 ----- left join
- 如何累计刷题的数目 ----- 窗口函数

源代码

with temp as (
    select l.user_id,l.date,number
    from login l
    left join passing_number pn on l.user_id = pn.user_id and l.date = pn.date
)
select name,date,sum(number) over(partition by name order by date) ps_num
from temp
join user on temp.user_id=user.id
where number is not null
order by date,name

笔试题72：考试分数(一)

详细描述：请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)

解题思路

通过题目中各个岗位分数的平均数可以知道按照岗位进行分组，然后求分数的平均值，并且保留3位有效数字：round(num, 3)

源代码

select job, round(avg(score),3) avg_score
from grade
group by job
order by avg_score desc

笔试题73：考试分数(二)

详细描述：请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

解题思路

看到这个题目，我们应该知道首先得知道该job的平均分数，所以需要用到第一题的那张表。
通过job字段和grade表进行join连接，然后查询条件是平均分数比分数要小

源代码

with temp as (
    select job, round(avg(score), 3) avg_score
    from grade
    group by job
    order by avg_score desc
)
select id, grade.job, score
from temp
         join grade on temp.job = grade.job
where temp.avg_score < grade.score
order by id;

笔试题74：考试分数(三)

详细描述：请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

解题思路

每个岗位分数前2名，首先就会想到开窗，得到每个分数的排名，然后查询出排名小于等于2的即可
然后将两个表进行join即可

源代码

with temp as (    select id,           language_id,           score,           dense_rank() over (partition by language_id order by score desc) rk    from grade)select temp.id, name, scorefrom temp         left join language on temp.language_id = language.idwhere rk <= 2order by name, score desc, id;

笔试题75：考试分数(四)

详细描述：请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

解题思路

floor函数：向下取整
下面这个解法记住就行了，用于求中位数位置的固定写法

源代码

select job,    floor(( count(*) + 1 )/ 2 ) start,    floor(( count(*) + 2 )/ 2 ) endfrom gradegroup by joborder by job;

笔试题76：考试分数(五)

解题思路：请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

解题思路

看见这个题目，我们首先就将题目要求进行拆分，先求中位数的位置，然后和有对应分数排名的中间表进行join，join条件是两个表的job相同，查询条件为中位数的起点等于排名或者中位数的终点等于排名

源代码

with temp as (    select job,           floor((count(*) + 1) / 2) start,           floor((count(*) + 2) / 2) end    from grade    group by job    order by job)select id, temp1.job, score, rkfrom (select id, job, score, row_number() over (partition by job order by score desc) rk      from grade) temp1         join temp on temp1.job = temp.jobwhere temp1.rk = temp.start   or temp1.rk = temp.endorder by temp1.id;

笔试题77：牛客的课程订单分析(一)

详细描述：请你写出一个sql语句查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序

解题思路

理清查询条件：购买时间为2025-10-15以后购买成功买的C++或者Java或者Python
开始居然没有写出来，写错的地方主要在最后一个查询条件，我们可以用in来解决选项的问题

源代码

select * from order_infowhere date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')order by id;

笔试题78：牛客的课程订单分析(二)

详细描述：请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序

解题思路

通过这个题目，我们只需要掌握语法顺序即可 where > group by > having > order by

源代码

select user_idfrom order_infowhere date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')group by user_idhaving count(user_id) >= 2order by user_id

笔试题79：牛客的课程订单分析(三)

详细描述：请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序

解题思路

order by 的列，必须是出现在group by 子句里的列因此只能在上面的基础上使用子查询
datediff(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数

源代码

select *from order_infowhere user_id in (        select user_id        from order_info        where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')        group by user_id        having count(*)>=2    ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')order by id;

笔试题80：牛客的课程订单分析(四)

详细描述：请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

解题思路

这个题目看起来非常复杂，但是我们有了上面的铺垫，就变的容易了很多，但是如果我们没有上面的铺垫的时候，我们是否能够将这个题目进行分解呢？这也是一种写sql的经验，过于复杂的题目，拆分成一个个中间表

源代码

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select user_id, min(date) first_buy_date, count(user_id) cntfrom tmpgroup by user_idorder by user_id;

笔试题81：牛客的课程订单分析(五)

详细描述：请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

解题思路

既要输出第一次购买，还要输出第二次购买，那么就必须知道对应的排名，因此我们可以利用开窗函数。

源代码

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select user_id, min(date), max(date), cntfrom(    select user_id, date, row_number() over (partition by user_id order by date) rk,       count(user_id) over(partition by user_id) cntfrom tmp        )tmp2where rk<=2group by user_id, cntorder by user_id;

笔试题82：牛客的课程订单分析(六)

详细描述：请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序

解题思路

依照题目的要求返回的字段，我们可以知道需要将两个表进行join，并且题目中说到如果是拼团订单，则显示NULL，因此我们用订单表 left join 客户端表

源代码

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select tmp.id,is_group_buy,namefrom tmpleft join client on tmp.client_id = client.idorder by id;

笔试题83：牛客的课程订单分析(七)

详细描述：请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序

解题思路

使用if语句替换null就可以了
这里再介绍一个函数IFNULL(expression, alt_value)：如果第一个参数的表达式 expression 为 NULL，则返回第二个参数

源代码

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select name_n,count(*)from (    select if(is_group_buy='Yes','GroupBuy',name) name_n    from tmp    left join client on tmp.client_id = client.id)tmp1group by name_norder by name_n;

笔试题84：实习广场投递简历分析(一)

详细描述：请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序。

解题思路

主要在于如何判断2025年，这有很多方式，可以用like语句，也可以用函数date_format(date,"%Y")
采用了sum里面可以传入表达式的方式

源代码

select    job,sum(if(year(date)=2025,num,0)) cntfrom resume_infogroup by joborder by cnt desc

笔试题85：实习广场投递简历分析(二)

详细描述：请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且先按月份降序排序，再按简历数目降序排序。

解题思路

弄清楚 每一个月每个岗位收到简历的数量是什么意思？我们就可以知道这个是按照岗位和月份进行分组，然后聚合简历数量

源代码

select    job,date_format(date,"%Y-%m") month,sum(num) cntfrom resume_infowhere year(date) = 2025group by job,monthorder by month desc, cnt desc

笔试题86：实习广场投递简历分析(三)

详细描述：请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示。

解题思路

根据前面两个题目的铺垫，我们很容易得到 2025年每一个月每个岗位收到简历的数量，那么2026年也是类似的。所以我们可以先写两个查询，一个是2025年，还有一个是2026年，最后将他们进行join连接，连接条件是什么呢？首先岗位要一样，其次就是同一个月份（题目是这样说的，同一个月同岗位）；仔细读题，就很容易求解

源代码

select tmp1.job,       first_year_mon,       first_year_cnt,       second_year_mon,       second_year_cntfrom (         select job,                date_format(date, "%Y-%m") first_year_mon,                sum(num)                   first_year_cnt         from resume_info         where year(date) = 2025         group by job, first_year_mon     ) tmp1         join (    select job,           date_format(date, "%Y-%m") second_year_mon,           sum(num)                   second_year_cnt    from resume_info    where year(date) = 2026    group by job, second_year_mon) tmp2 on tmp1.job = tmp2.job and right(first_year_mon, 2) = right(second_year_mon, 2)order by first_year_mon desc, job desc

笔试题87：最差是第几名(一)

详细描述：请你写出一个SQL查询，如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照grade升序排序

解题思路

题目要求按照grade升序排序，实际上就是按照综合成绩升序排序，因为综合成绩在后面的grade越大
第二行的成绩等于第一行加上原始的第二行，开窗即可

源代码

select grade, sum(number) over(order by grade)from class_grade;

笔试题88：最差是第几名(二)

详细描述：老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出

解题思路

题解里面利用了这一点：当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数
如果知道上述的结论，我们正序累计已经有了，逆序累计加上desc就可以了

源代码

select gradefrom (         select grade,                #(select sum(number) from class_grade) as total,    			sum(number) over() total,                sum(number) over (order by grade)        a,                sum(number) over (order by grade desc)   b         from class_grade     ) tmpwhere a >= total / 2  and b >= total / 2order by grade

代码里面还有一点值得学习的地方，select中可以放select语句，因为聚合函数不能和非聚合字段放在一起，所以我们重写一个select查询出这个聚合值

笔试题89：获得积分最多的人(一)

详细描述：请你写一个SQL查找积分增加最高的用户的名字，以及他的总积分是多少(此题数据保证积分最高的用户有且只有1个)

解题思路

查找积分增加最多的用户，首先就需要把该用户增加的所有积分累加，怎么累加？分组累加

源代码

select name, cntfrom (         select user_id, sum(grade_num) cnt         from grade_info         group by user_id         order by cnt desc         limit 1     ) tmp         join user on tmp.user_id = user.id;

笔试题90：获得积分最多的人(二)

详细描述：请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序

解题思路

当查询中两次以上用到中间表时，我们可以采用with 中间表名 as (select 子句)建立临时表

源代码

with tmp_table as (    select user_id, sum(grade_num) cnt    from grade_info    group by user_id    order by cnt desc)select user_id, name, cntfrom tmp_tablejoin user on user.id = tmp_table.user_idwhere cnt = (    select max(cnt) max_cnt    from tmp_table)

笔试题91：获得积分最多的人(三)

详细描述：请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序

解题思路

有了上面的思路，这一题唯一需要解决的问题时，当type=“reduce”，不应该加上该积分，而是减去该积分

源代码

with tmp_table as (    select user_id, sum(grade_num) cnt    from (select user_id, if(type = "reduce", -grade_num, grade_num) grade_num from grade_info) tmp1    group by user_id)select id, name, cntfrom tmp_table         join user on tmp_table.user_id = user.idwhere cnt = (    select max(cnt)    from tmp_table)order by id

码农公寓

笔试题66：牛客每个人最近的登录日期(一)

解题思路

源代码

笔试题67：牛客每个人最近的登录日期(二)

解题思路

源代码

笔试题68：牛客每个人最近的登录日期(三)【留存率】**

解题思路

源代码

补充知识

笔试题69：牛客每个人最近的登录日期(四)

解题思路

源代码

笔试题70：牛客每个人最近的登录日期(五)

解题思路

源代码

笔试题71：牛客每个人最近的登录日期(六)

解题思路

源代码

笔试题72：考试分数(一)

解题思路

源代码

笔试题73：考试分数(二)

解题思路

源代码

笔试题74：考试分数(三)

解题思路

源代码

笔试题75：考试分数(四)

解题思路

源代码

笔试题76：考试分数(五)

解题思路

源代码

笔试题77：牛客的课程订单分析(一)

解题思路

源代码

笔试题78：牛客的课程订单分析(二)

解题思路

源代码

笔试题79：牛客的课程订单分析(三)

解题思路

源代码

笔试题80：牛客的课程订单分析(四)

解题思路

源代码

笔试题81：牛客的课程订单分析(五)

解题思路

源代码

笔试题82：牛客的课程订单分析(六)

解题思路

源代码

笔试题83：牛客的课程订单分析(七)

解题思路

源代码

笔试题84： 实习广场投递简历分析(一)

解题思路

源代码

笔试题85： 实习广场投递简历分析(二)

解题思路

源代码

笔试题86： 实习广场投递简历分析(三)

解题思路

源代码

笔试题87： 最差是第几名(一)

解题思路

源代码

笔试题88： 最差是第几名(二)

解题思路

源代码

笔试题89： 获得积分最多的人(一)

解题思路

源代码

笔试题90： 获得积分最多的人(二)

解题思路

源代码

笔试题91： 获得积分最多的人(三)

解题思路

源代码

笔试题84：实习广场投递简历分析(一)

笔试题85：实习广场投递简历分析(二)

笔试题86：实习广场投递简历分析(三)

笔试题87：最差是第几名(一)

笔试题88：最差是第几名(二)

笔试题89：获得积分最多的人(一)

笔试题90：获得积分最多的人(二)

笔试题91：获得积分最多的人(三)