牛客SQL题答案（持续更新）_试卷的类别tag可能出现大小写混乱的情况,请先筛选出试卷作答数小于threshold的类-CSDN博客

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