with t as (select distinct device_id, date from question_practice_detail)
select count(*)/(select count(*) from t) as avg_ret from t a 
join t b on a.device_id = b.device_id and datediff(b.date, a.date) = 1;

SQL34 统计复旦用户8月练题情况

select a.device_id, university,
count(question_id) as question_cnt, 
sum(if(result='right', 1, 0)) as right_question_cnt
from user_profile a left join question_practice_detail b on a.device_id = b.device_id and
 month(date) = 8 where university = '复旦大学' group by a.device_id, university;

SQL35 浙大不同难度题目的正确率

select difficult_level, sum(if(result='right', 1, 0))/count(*) as correct_rate
from user_profile left join question_practice_detail using(device_id) join question_detail
using(question_id) where university = '浙江大学' group by difficult_level order by correct_rate;

SQL进阶挑战

SQL14 SQL类别高难度试卷得分的截断平均值

select tag, difficulty,
round((sum(score)-max(score)-min(score))/(count(*)-2), 1) as clip_avg_score
from exam_record join examination_info using(exam_id)
where score is not null and tag = 'SQL' and difficulty = 'hard'
;

SQL19 未完成试卷数大于1的有效用户

select uid, sum(if(submit_time is null, 1, 0)) as incomplete_cnt,
sum(if(submit_time is not null, 1, 0)) as complete_cnt,
group_concat(distinct concat(date(start_time), ':', tag) order by start_time SEPARATOR ';') as detail
from exam_record join examination_info using(exam_id) where year(start_time) = 2021 
group by uid
having complete_cnt >= 1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc;

SQL20 月均完成试卷数不小于3的用户爱作答的类别

select tag, count(*) as tag_cnt from exam_record join examination_info using(exam_id)
where uid in 
(select uid
from exam_record 
group by uid, date_format(start_time, '%Y%m') 
having count(submit_time) >= 3) group by tag order by tag_cnt desc;

SQL25 满足条件的用户的试卷完成数和题目练习数

with t as (
select uid from exam_record 
where exam_id in (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard') and uid in (select uid from user_info where level = 7) group by uid having avg(score) > 80
)
select a.uid, max(exam_cnt) as exam_cnt, max(ifnull(question_cnt, 0)) as question_cnt from
(select uid, count(exam_id) over (partition by uid, date(start_time)) as exam_cnt from exam_record where year(start_time) = 2021 and uid in (select * from t)) a
left join
(select uid, count(question_id) over (partition by uid, date(submit_time)) as question_cnt from practice_record
where year(submit_time) = 2021 and uid in (select * from t)) b
using(uid) group by a.uid order by exam_cnt, question_cnt desc
;

SQL26 每个6/7级用户活跃情况

# 所有日期都要去重
select uid, ifnull(s1.act_month_total, 0) as act_month_total,
ifnull(s2.act_days_2021, 0) as act_days_2021,
count(distinct(if(year(a.submit_time)=2021, date(a.submit_time), null))) as act_days_2021_exam,
count(distinct(if(year(b.submit_time)=2021, date(b.submit_time), null))) as act_days_2021_question
from user_info left join exam_record a using(uid) left join practice_record b using(uid)left join (select uid, count(mo) as act_month_total	from
(select distinct uid, date_format(submit_time, '%Y%m') as mo from exam_record
union
select distinct uid, date_format(submit_time, '%Y%m') as mo from practice_record
) s group by uid) s1 using(uid)
left join (select uid, count(mo) as act_days_2021 from
(select distinct uid, date(submit_time) as mo from exam_record
union
select distinct uid, date(submit_time) as mo from practice_record) s 
where year(mo) = 2021 group by uid) s2 using(uid)
where level in (6, 7) group by uid order by act_month_total desc, act_days_2021 desc;

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

with t as (
select exam_id, duration, release_time, timestampdiff(minute, start_time, submit_time) as te
from exam_record join examination_info using(exam_id)
)

select exam_id, duration, release_time from
(select exam_id, te,duration,release_time, row_number() over (partition by exam_id order by te desc) as rk from t
union
select exam_id, te,duration,release_time, row_number() over (partition by exam_id order by te) as rk from t) s where rk = 2 
group by exam_id, duration, release_time having max(te)-min(te)>duration/2
order by exam_id desc;

SQL29 连续两次作答试卷的最大时间窗

select uid, days_window, round(cnt/(du+1)*days_window, 2) as avg_exam_cnt from
(select uid, count(distinct exam_id, date(start_time)) as cnt, 
datediff(max(start_time), min(start_time)) as du from exam_record 
where year(start_time)=2021 group by uid) a join
(select uid, max(du)+1 as days_window from
(select uid, datediff(lead(dt, 1) over(partition by uid order by dt), dt) as du from (select distinct uid, date(start_time) as dt from exam_record 
where submit_time is not null and year(start_time) = 2021 order by uid, dt) s) s1 group by uid having days_window is not null) b
using(uid) order by days_window desc, avg_exam_cnt desc;

SQL31 未完成率较高的50%用户近三个月答卷情况

with t as (
select uid from
(select uid, percent_rank() over (order by per) as rk from
(select uid, if(count(distinct date(submit_time))=0, 1, 
if(count(distinct date(submit_time))=count(*), 0, count(distinct date(submit_time))))/count(*) as per from exam_record left join examination_info using(exam_id) left join user_info using(uid) where tag = 'SQL' group by uid) s) s1 where rk >= 0.5
and uid in (select uid from user_info where level in (6, 7))
)
select uid, start_month, total_cnt, complete_cnt from
(select uid, date_format(start_time, '%Y%m') as start_month,
count(*) as total_cnt, count(submit_time) as complete_cnt,
dense_rank() over (partition by uid order by date_format(start_time, '%Y%m') desc) as rk
from exam_record where uid in (select * from t)
group by uid, start_month) s where rk <= 3 order by uid, start_month;

SQL32 试卷完成数同比2020年的增长率及排名变化

select tag, max(if(year=2020, exam_cnt, 0)) as exam_cnt_20,
max(if(year=2021, exam_cnt, 0)) as exam_cnt_21,
concat(round((max(if(year=2021, exam_cnt, 0))-max(if(year=2020, exam_cnt, 0)))/max(if(year=2020, exam_cnt, 0))*100, 1), '%') as growth_rate,
max(if(year=2020, rk, 0)) as exam_cnt_rank_20, max(if(year=2021, rk, 0)) as exam_cnt_rank_21, 
max(if(year=2021, rk, 0))-max(if(year=2020, rk, 0)) as rank_delta
from (select *, rank() over (partition by year order by exam_cnt desc) as rk from
(select tag, year(start_time) as year, count(submit_time) as exam_cnt
from exam_record join examination_info using(exam_id)
where year(start_time) in (2020, 2021) and month(start_time) <= 6
group by tag, year having exam_cnt != 0) s) s1 group by tag having count(*) > 1
order by exam_cnt_20, growth_rate desc, exam_cnt_21 desc, exam_cnt_rank_21 desc;

SQL35 每月及截止当月的答题情况

with t as (
select date_format(start_time, '%Y%m') as mo,
count(distinct uid) as mau from exam_record
group by mo
)

select mo as start_month, mau, month_add_uv, max(month_add_uv) over (order by mo) as 
max_month_add_uv, sum(month_add_uv) over (order by mo) as cum_sum_uv from
(select mo, mau, month_add_uv from t join 
(select mo, sum(if(mo=minm, 1, 0)) as month_add_uv from
(select distinct uid, date_format(start_time, '%Y%m') as mo, 
min(date_format(start_time, '%Y%m')) over (partition by uid) as minm
from exam_record) s group by mo) s using(mo)) s
order by start_month;

SQL38 筛选限定昵称成就值活跃日期的用户

select uid, nick_name, achievement from
user_info left join exam_record a using(uid) left join practice_record b using(uid)
where left(nick_name, 2) = '牛客' and right(nick_name, 1) = '号'
and achievement BETWEEN 1200 and 2500
group by uid, nick_name, achievement 
having DATE_FORMAT(max(a.submit_time), '%Y%m') = '202109'
or DATE_FORMAT(max(b.submit_time), '%Y%m') = '202109'
;

SQL39 筛选昵称规则和试卷规则的作答记录

select uid, exam_id, round(sum(score)/count(submit_time)) as avg_score
from user_info left join exam_record using(uid) join examination_info using(exam_id)
where left(tag, 1) in ('c', 'C') and nick_name RLIKE '^牛客[0-9]{1,}号$|^[0-9]{1,}$'
group by uid, exam_id having avg_score is not null
order by uid, avg_score;

SQL40 根据指定记录是否存在输出不同情况

with t as (
select count(distinct uid) as cnt
from user_info left join exam_record using(uid)
where level = 0 group by uid having sum(if(submit_time is null, 1, 0)) > 2
)
select uid, ifnull(sum(if(submit_time is null and start_time is not null, 1, 0)), 0) as incomplete_cnt,
ifnull(round(sum(if(submit_time is null and start_time is not null, 1, 0))/count(*), 3), 0) as incomplete_rate
from user_info left join exam_record using(uid) where level = 0 group by uid 
union
select uid, ifnull(sum(if(submit_time is null and start_time is not null, 1, 0)), 0) as incomplete_cnt,
ifnull(round(sum(if(submit_time is null and start_time is not null, 1, 0))/count(*), 3), 0) as incomplete_rate
from user_info left join exam_record using(uid) where (select * from t) is null and uid in (select uid from exam_record) group by uid order by incomplete_rate
;

SQL41 各用户等级的不同得分表现占比

with t as (
select level, 
if(score>=90, '优', if(score>=75, '良', if(score>=60, '中', '差'))) as score_grade, count(*) as cnt
from user_info join exam_record using(uid)
where score is not null group by level, score_grade
)
select level, score_grade, round(cnt/total, 3) as ratio from
(select *, sum(cnt) over (partition by level) as total from t) s
order by level desc, ratio desc;

SQL43 注册当天就完成了试卷的名单第三页

with t as (select uid from user_info where (uid, date(register_time)) in
(select uid, min(date(start_time)) as dt from exam_record where exam_id in (select exam_id from examination_info where tag = '算法') group by uid))
select uid, level, register_time, max(score) as max_score
from exam_record join user_info using(uid) where uid in (select * from t) 
group by uid, level, register_time having max_score is not null
order by max_score desc limit 7, 3;

SQL46 大小写混乱时的筛选统计

解法一
# with t as (
# select tag, count(*) as cnt
# from exam_record join examination_info using(exam_id) 
# group by tag having cnt < 3
# )
# select lower(tag) as tag, count(*) as answer_cnt from exam_record join examination_info using(exam_id)
# where lower(tag) in (select tag from t) and tag not in (select tag from t) group by tag 
# union
# select tag, count(*) as answer_cnt from exam_record a join examination_info using(exam_id) 
# group by tag
# having (upper(tag), answer_cnt) in (select * from t) and tag != upper(tag)
# order by answer_cnt desc
# ;

解法二
with t as (
select tag, count(*) as answer_cnt
from exam_record join examination_info using(exam_id)
group by tag 
)
# 找出大写的试卷作答数
select a.tag, b.answer_cnt
from t a join t b
on upper(a.tag) = b.tag and a.tag != b.tag and a.answer_cnt < 3;

全部题目

SQL12 获取每个部门中当前员工薪水最高的相关信息

select dept_no, emp_no, salary as maxSalary
from dept_emp join salaries using(emp_no, to_date)
where (dept_no, salary) in 
(select dept_no, max(salary) from dept_emp join salaries using(emp_no) group by dept_no)
order by dept_no;

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

select emp_no, salary, last_name, first_name from employees join salaries using(emp_no)
where salary in (select max(salary) from salaries where salary not in (select max(salary) from salaries)) and to_date ='9999-01-01';

SQL21 查找在职员工自入职以来的薪水涨幅情况

select emp_no, s2-s1 as growth
from (select emp_no, salary as s1 from salaries where from_date in (select hire_date from employees)) s1
join (select emp_no, salary as s2 from salaries where to_date = '9999-01-01') s2 using(emp_no)
order by growth;

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

select emp_no, salary, dense_rank() over (order by salary desc) as t_rank from salaries where to_date = '9999-01-01'
order by t_rank, emp_no;

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

select dept_no, emp_no, salary
from employees join dept_emp using(emp_no) join salaries using(emp_no)
where emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01') and 
dept_emp.to_date = '9999-01-01' and salaries.to_date = '9999-01-01';

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

select s1.emp_no, s2.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary from
(select c.emp_no, a.dept_no, salary from dept_emp a
join dept_manager b on a.dept_no = b.dept_no and a.emp_no != b.emp_no join salaries c on a.emp_no = c.emp_no) s1 
join (select emp_no, dept_no, salary from dept_manager join salaries using(emp_no)) s2
using(dept_no) where s1.salary > s2.salary;

SQL26 汇总各个部门当前员工的title类型的分配数目

select dept_no, dept_name, title, count(*) as count
from titles join dept_emp using(emp_no, to_date) join departments using(dept_no)
group by dept_no, dept_name, title
order by dept_no, title;

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

select emp_no, first_name, last_name, btype, salary,
if(btype=1, round(salary*0.1, 1), if(btype=2, round(salary*0.2, 1), round(salary*0.3, 1))) as bonus
from employees join emp_bonus using(emp_no) join salaries using(emp_no)
where to_date = '9999-01-01' order by emp_no;

SQL60 统计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';

SQL61 给出employees表中排名为奇数行的first name

select first_name as first from employees
where first_name in (select first_name
from (select first_name, row_number() over (order by first_name) rk from employees) s where rk % 2 = 1);

SQL65 异常的邮件概率

select date, 
round(sum(if(type='no_completed' and send_id not in (select id from user where is_blacklist = 1) and receive_id not in (select id from user where is_blacklist = 1), 1, 0))/sum(if(send_id not in (select id from user where is_blacklist = 1) and receive_id not in (select id from user where is_blacklist = 1), 1, 0)), 3) as p
from email group by date order by date;

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

select b.name as u_n, c.name as c_n, date
from login a join user b on a.user_id = b.id join client c on a.client_id = c.id
where (user_id, date) in (select user_id, max(date) from login group by user_id)
order by u_n
;

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

select round((select count(distinct a.user_id) as cnt from login a join login b on a.user_id = b.user_id and datediff(b.date, a.date) = 1)/(select sum(if(date=dt, 1, 0)) as cnt from
(select user_id, date, min(date) over (partition by user_id) as dt from login) s), 3) as p
;

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

select date, sum(if(date=mind, 1, 0)) as new from
(select user_id, date, min(date) over (partition by user_id) as mind from login) s
group by date order by date;

SQL70 牛客每个人最近的登录日期(五)

-- 每个日期"新用户"的留存率
select date, round(ifnull(a.cnt/b.new, 0), 3) as p from (select a.date, count(*) as cnt from login a join login b on a.user_id = b.user_id and datediff(b.date, a.date) = 1 where (a.user_id, a.date) in (select user_id, min(date) from login group by user_id) group by a.date) a right join (select date, sum(if(date=mind, 1, 0)) as new from (select date, min(date) over (partition by user_id) as mind from login) s group by date) b using(date) order by date;

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

select name as u_n, date, sum(number) over (partition by name order by date) as ps_num from
(select distinct name, b.date, number
from login a join passing_number b using(user_id) join user c on a.user_id = c.id) s
order by date, u_n
;

SQL74 考试分数(三)

select id, name, score from 
(select a.id, name, score, dense_rank() over (partition by name order by score desc) as rk from grade a join language b on a.language_id = b.id) s where rk <= 2
order by name, score desc, id;

SQL75 考试分数(四)

select job, ceil(cnt/2) as start, ceil(cnt/2) as end from
(select *, count(*) over (partition by job) as cnt from grade) s
where cnt % 2 = 1
union
select job, ceil(cnt/2) as start, ceil(cnt/2)+1 as end from
(select *, count(*) over (partition by job) as cnt from grade) s
where cnt % 2 = 0
order by job;

SQL76 考试分数(五)

select id, job, score, rk as t_rank from
(select *, row_number() over (partition by job order by score desc) rk, count(*) over (partition by job) as cnt from grade) s where cnt % 2 = 1 and rk = ceil(cnt/2)
union
select id, job, score, rk as t_rank from
(select *, row_number() over (partition by job order by score desc) rk, count(*) over (partition by job) as cnt from grade) s where cnt % 2 = 0 and rk between ceil(cnt/2) and ceil(cnt/2)+1
order by id;

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

select user_id, min(date) as first_buy_date, sum(if(status='completed', 1, 0)) as cnt
from order_info
where date > '2025-10-15' and product_name in ('C++', 'Java', 'Python')
group by user_id having cnt >= 2 order by user_id;

SQL81 牛客的课程订单分析(五)

select user_id, max(if(rk=1, date, 0)) as first_buy_date, max(if(rk=2, date, 0)) as second_buy_date,
count(*) as cnt from
(select user_id, product_name, date, status, DENSE_RANK() over (partition by user_id order by date) as rk
 from order_info where date > '2025-10-15' and product_name in ('C++', 'Java', 'Python') and status = 'completed') s group by user_id having cnt >= 2 order by user_id
;

SQL82 牛客的课程订单分析(六)

解法一
# select a.id, is_group_buy, b.name as client_name
# from order_info a left join client b on a.client_id = b.id
# where a.user_id in (select user_id from order_info 
# where date > '2025-10-15' and product_name in ('C++', 'Java', 'Python') 
# group by user_id having sum(if(status='completed', 1, 0)) >= 2) and date > '2025-10-15'
# and product_name in ('C++', 'Java', 'Python') and status = 'completed'
# order by a.id;

解法二
select id, is_group_buy, client_name from
(select a.id, is_group_buy, b.name as client_name, 
count(*) over (partition by user_id) as cnt from
order_info a left join client b on a.client_id = b.id 
where date > '2025-10-15' and product_name in ('C++', 'Java', 'Python') and status = 'completed') s
where cnt >= 2 order by id;

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

select source, count(*) as cnt from
(select ifnull(b.name, 'GroupBuy') as source, a.is_group_buy, count(*) over (partition by user_id) as cnt
from order_info a left join client b on a.client_id = b.id
where date > '2025-10-15' and product_name in ('C++', 'Java', 'Python') and status = 'completed') s
where cnt >= 2 group by source order by source;

SQL86 实习广场投递简历分析(三)

with t as (
select distinct job, date_format(date, '%Y-%m') as dt, sum(num) over (partition by job, date_format(date, '%Y-%m')) as num from resume_info where year(date) in (2025, 2026)
)
select a.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt from
(select job, dt as first_year_mon, num as first_year_cnt from t where left(dt, 4)=2025) a
join (select job, dt as second_year_mon, num as second_year_cnt from t where left(dt, 4)=2026) b
on a.job = b.job and right(first_year_mon, 2) = right(second_year_mon, 2) order by first_year_mon desc, job desc
;

SQL88 最差是第几名(二)

with t as (select grade, sum(number) over (order by grade) as place, (select sum(number) from class_grade) as total from class_grade)
select grade from t where place between ceil(total/2) and total-1 and total % 2 = 0
union
select min(grade) as grade from t where place between ceil(total/2) and total-1 and total % 2 = 1
having grade is not null;

SQL90 获得积分最多的人(二)

select id, name, grade_num from
(select *, dense_rank() over (order by grade_num desc) as rk from
(select distinct user_id as id, name, sum(grade_num) over (partition by user_id) as grade_num
from grade_info a join user b on a.user_id = b.id) s) s1 where rk = 1 order by id;

SQL91 获得积分最多的人(三)

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

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

select music_name
from music
where id not in (select music_id from music_likes where user_id = 1)
and id in (select music_id from music_likes where user_id in (select follower_id from follow where user_id = 1)) order by id;

原文链接：https://blog.csdn.net/qq_45580637/article/details/125471243