牛客网【SQL】经典难题

  难 130 请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序

select er.exam_id,
    count(distinct er.uid) uv, #用户作答人数
    round(avg(score),1) avg_score
from examination_info ei
join exam_record er
on ei.exam_id=er.exam_id
where ei.tag='SQL'
#限制sql发卷当天的作答
and date_format(ei.release_time,'%Y%m%d')=date_format(er.start_time,'%Y%m%d')
and er.uid in (select uid
              from user_info
              where level>5)
group by er.exam_id
order by uv desc,avg_score asc;

132 请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示

select exam_id tid,
    count(distinct uid) uv,
    count(exam_id) pv
from exam_record
group by tid
union all
select question_id tid,
    count(distinct uid) uv,
    count(question_id) pv
from practice_record
group by tid

order by  left(tid,1) desc,uv desc,pv desc

 133 请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

#2021年里，所有每次试卷得分都能到85分的人
select uid,
    'activity1' activity
from exam_record er
where year(start_time)='2021'
group by uid
having min(score)>=85
union all
#2021年里，至少有一次用了一半时间就完成高难度试卷且分数大于80的人
select uid,
    'activity2' activity
from exam_record er
left join examination_info ei
using(exam_id)
where year(start_time)='2021' 
and ei.difficulty='hard' 
and score>=80 
#至少有一次用了一半时间就完成，timestampdiff计算时间差
and timestampdiff(second,er.start_time,er.submit_time)<=ei.duration*30
group by uid
order by uid

困难134 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

select uid,
    count(distinct er.exam_id) exam_cnt,
    count(distinct pr.submit_time) question_cnt
from exam_record er
left join practice_record pr
using(uid)
where uid in (select er.uid
             from exam_record er
             join user_info ui 
             using(uid)
             join examination_info ei
             using(exam_id)
             where difficulty='hard'
             and tag='SQL'
             and ui.level=7
             group by er.uid
             having avg(score)>80)
and year(er.submit_time)=2021
and er.score is not null
group by er.uid
order by exam_cnt,question_cnt desc

困难135  请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序

select user_info.uid,
        count(distinct act_month) as act_month_total,
        count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
        count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
        count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
from 
    (SELECT uid,
        exam_id as ans_id,
        start_time as act_time,
        date_format(start_time,'%Y%m') 
    as act_month, 
        date_format(start_time,'%Y%m%d') as act_day,
        'exam' as tag 
from exam_record
UNION ALL
select uid,
    question_id as ans_id,
    submit_time as act_time,
    date_format(submit_time,'%Y%m') as act_month, 
    date_format(submit_time,'%Y%m%d') as act_day,
    'question' as tag from practice_record ) total 
right join user_info on total.uid=user_info.uid
where user_info.level in (6,7)
group by total.uid
order by act_month_total desc,act_days_2021 desc

137 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

#先将用时时间排序，正序和倒叙两种，然后取出正序第二，倒叙第二，将两张表关联，
然后用倒叙第二减正序第二大于试卷时长一半即可，最后根据试卷id倒叙
select  a.exam_id,a.duration,a.release_time from (
select a.exam_id,a.duration,bb-aa cc,a.release_time from (
select a.exam_id,a.duration,a.release_time, timestampdiff(second,start_time,submit_time) aa,
rank() over(partition by b.exam_id order by timestampdiff(second,start_time,submit_time)) short
from examination_info a
left join exam_record b
on a.exam_id = b.exam_id) a
left join (
select * from (
select a.exam_id,a.duration, timestampdiff(second,start_time,submit_time) bb,
rank() over(partition by b.exam_id order by timestampdiff(second,start_time,submit_time) desc) lon
from examination_info a
left join exam_record b
on a.exam_id = b.exam_id) a
where a.lon = 2) b
on a.exam_id = b.exam_id
where a.short = 2) a
where cc > a.duration*30
order by exam_id desc

138 请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。

SELECT uid, days_window, round(days_window*exam_cnt/diff_days, 2) as avg_exam_cnt
FROM (
    SELECT uid,
        count(start_time) as exam_cnt,  # 此人作答的总试卷数
        DATEDIFF(max(start_time), min(start_time))+1 as diff_days, # 最早一次作答和最晚一次作答的相差天数
        max(DATEDIFF(next_start_time, start_time))+1 as days_window # 两次作答的最大时间窗
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time) over(partition by uid ORDER BY start_time) as next_start_time # 将连续的下次作答时间拼上
        FROM exam_record
        WHERE year(start_time)=2021
    ) as t_exam_record_lead
    GROUP BY uid
) as t_exam_record_stat
WHERE diff_days>1
ORDER BY days_window DESC, avg_exam_cnt DESC