SQL牛客网刷题03——较难

目录

1、获取当前薪水第二多的员工的emp_no以及其对应的薪水

2、 对所有员工的薪水按照salary降序进行1-N的排名

3、获取所有非manager员工当前的薪水情况

4、 获取有奖金的员工相关信息

5、统计salary的累计和running_total

6、给出employees表中排名为奇数行的first_name

7、异常的邮件概率

8、牛客每个人最近的登录日期(二)

9、牛客每个人最近的登录日期(三)

10、牛客每个人最近的登录日期(四)

11、牛客每个人最近的登录日期(六)

12、考试分数(三)

13、考试分数(四)

14、牛客的课程订单分析(四)

15、 牛客的课程订单分析(七)

16、 最差是第几名(二)

17、获得积分最多的人(二)

18、 网易云音乐推荐(网易校招笔试真题)

---

1、获取当前薪水第二多的员工的emp_no以及其对应的薪水

select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <   
        (
        select max(salary)    -- 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'  
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01

2、 对所有员工的薪水按照salary降序进行1-N的排名

select emp_no, salary,
dense_rank() over(order by salary desc) t_rank
from salaries
order by t_rank,emp_no asc;

3、获取所有非manager员工当前的薪水情况

select de.dept_no, e.emp_no, s.salary
from employees e, dept_emp de, salaries s
where e.emp_no = de.emp_no 
and e.emp_no = s.emp_no 
and e.emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01')

4、 获取有奖金的员工相关信息

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
(case eb.btype
 when 1 then s.salary * 0.1
 when 2 then s.salary * 0.2
 else s.salary * 0.3
 end) as bonus
from employees as e
join emp_bonus as eb on e.emp_no = eb.emp_no
join salaries as s on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
order by emp_no;

5、统计salary的累计和running_total

select emp_no,
    salary,
    sum(salary) over(order by emp_no) as running_total
from salaries
where to_date="9999-01-01"

6、给出employees表中排名为奇数行的first_name

select e.first_name
from employees as e
join (
    select first_name,
    row_number() over (order by first_name) as rk_name
    from employees) as r
on e.first_name = r.first_name
where r.rk_name % 2 = 1

7、异常的邮件概率

select e.date
	   ,round(sum(case type when 'no_completed' then 1 else 0 end)/count(e.id),3) as p
from email e
join user u1 on e.send_id = u1.id 
join user u2 on u2.id = e.receive_id
where u1.is_blacklist = 0 and u2.is_blacklist=0
group by e.date
order by e.date;

8、牛客每个人最近的登录日期(二)

查询每个用户最近一天登录日子，用户的名字，以及用户用的设备的名字

select u.name u_n,c.name c_n,l.date from login l
inner join user u on l.user_id = u.id
inner 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_n
# select 子句中只能存在以下三种元素：常数、聚合函数、group by子句指定列(聚合键)

9、牛客每个人最近的登录日期(三)

查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率

# 新登录用户的次日成功的留存率
# 解题公式：（第一天登录的新用户并且第二天也登录的用户）/（总用户）
# 总用户数量：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(DISTINCT user_id)*1.0 / (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);

10、牛客每个人最近的登录日期(四)

查询每个日期登录新用户个数

select t.date,
       sum(case when t.t_rank =1 then 1 else 0 end) new
from
(
    select date,
           row_number() over(partition by user_id order by date) t_rank
    from login
) t
group by t.date

11、牛客每个人最近的登录日期(六)

select u.name,
       pn.date,
       sum(number) over(partition by user_id order by date) as ps_num
from passing_number pn join user u
                       on pn.user_id=u.id
order by pn.date,u.name

12、考试分数(三)

每个岗位分数排名前2名的用户

SELECT a.id, l.name, a.score
FROM language AS l
JOIN
(SELECT id, language_id, score, 
 dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num
FROM grade)a
ON l.id = a.language_id
WHERE rank_num <=2
ORDER BY l.name, a.score DESC, a.id;

13、考试分数(四)

查询各个岗位分数升序排列之后的中位数位置的范围

# 方法一
# FLOOR(x) 返回小于或等于 x 的最大整数.
# sum(1)跟count(*)是一个意思都是对获取总行数的意思
select job ,
       floor((sum(1)+1)/2) as start,
       floor((sum(1)+2)/2) as end
from grade
group by job
order by job

# 方法二
with t1 as
(
    select job, count(score) as num
    from grade
    group by job
)
 
select job,
    (case when num%2=0 then ceil(num/2) else ceil(num/2) end)as start,
    (case when num%2=0 then ceil(num/2+1) else ceil(num/2) end) as end
from t1
order by job

14、牛客的课程订单分析(四)

查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id

# 在2025-10-15之后
# 有一个用户下单2个以及2个以上
# 状态为购买成功
# C++课程或Java课程或Python课程
# 第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date
# 购买成功的C++课程或Java课程或Python课程的次数cnt
 
select user_id, min(date) , count(id)
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(id) > 1
order by user_id asc;

15、 牛客的课程订单分析(七)

在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单

select (case when is_group_buy = 'No'
        then c.name
        else 'GroupBuy'
        end) as source,
        count(t.id) as cnt
from (select *,
      count(id)over(partition by user_id) as num
      from order_info
      where date > '2025-10-15'
      and status = 'completed'
      and product_name in ('C++','Java','Python')) as t
left join client c on t.client_id = c.id
where t.num >= 2
group by source
order by source

16、 最差是第几名(二)

合成绩的中位数是什么档位

# 当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数
select grade from (
    select grade,
        (select sum(number) from class_grade) as total,
        sum(number) over(order by grade) a,
        sum(number) over(order by grade desc) b
    from class_grade) t1
where a >= total/2 and b >=total/2
order by grade;

17、获得积分最多的人(二)

查找积分增加最高的用户的id(可能有多个)

select u.id,u.name,t.grade_sum
from (select user_id,
             sum(grade_num) as grade_sum,
             rank()over(order by sum(grade_num) desc) as a
      from grade_info
      group by user_id) as t
join user u
on u.id = t.user_id
where t.a = 1

18、 网易云音乐推荐(网易校招笔试真题)

查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐

select music_name
from (
 select distinct music_id from music_likes
 where user_id in (select follower_id from follow where user_id = 1)
 and music_id not in (select music_id from music_likes where user_id = 1)
) a
join music m on a.music_id = m.id
 order by music_id