1.SQL类别高难度试卷得分的截断平均值
题目:从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值(去掉一个最大值和一个最小值后的平均值)。
即得出下面这个结论:
注:示例数据中exam(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间)
exam表内容如下:
id | exam_id | tag | difficulty | duration | release_time |
1 | 9001 | SQL | hard | 60 | 2020-01-01 10:00:00 |
2 | 9002 | 算法 | medium | 80 | 2020-08-02 10:00:00 |
注:示例数据:exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分)
exam_record表内容如下
id | exam_id | start_time | submit_time | score |
1 | 9001 | 2020/1/2 9:01 | 2020/1/2 9:21 | 80 |
2 | 9001 | 2021/5/2 10:01 | 2021/5/2 10:30 | 81 |
3 | 9001 | 2021/6/2 19:01 | 2021/6/2 19:31 | 84 |
4 | 9002 | 2021/9/5 19:01 | 2021/9/5 19:40 | 89 |
5 | 9001 | 2021/9/2 12:01 | (null) | (null) |
6 | 9002 | 2021/9/1 12:01 | (null) | (null) |
7 | 9002 | 2021/2/2 19:01 | 2021/2/2 19:30 | 87 |
8 | 9001 | 2021/5/5 18:01 | 2021/5/5 18:59 | 90 |
9 | 9001 | 2021/9/7 12:01 | 2021/9/7 10:31 | 50 |
10 | 9001 | 2021/9/6 10:01 | (null) | (null) |
select tag,difficulty,round(((sum(score)-max(score)-min(score))/(count(score)-2)),1) as avg_score
from exam,exam_record
where exam.exam_id = exam_record.exam_id
and score not in ('(null)')
group by exam_record.exam_id
having count(exam_record.exam_id)>3
执行运行结果如下:
2.满足条件的用户的试卷完成数和题目练习数
题目:找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬,统计他们的2021年试卷总完成次数和题目总练习次数,只保留2021年有试卷完成记录的用户。结果按试卷完成数升序,按题目练习数降序。展示如下:
注:用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):
注:试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
注:试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
注:题目练习记录表practice_record(uid用户ID, question_id题目ID, submit_time提交时间, score得分):
WITH a AS ( SELECT uid, count( exam_id ) AS exam_cnt FROM exam_record GROUP BY uid ),
c AS (
SELECT
uid
FROM
exam_record
JOIN examination_info USING ( exam_id )
JOIN user_info USING ( uid )
WHERE
tag = 'SQL'
AND LEVEL = 7
GROUP BY
uid
HAVING
avg( score ) > 80
) SELECT
c.uid,
exam_cnt,
IF
( question_cnt1 IS NULL, 0, question_cnt1 ) as question_cnt
FROM
a,
c
LEFT JOIN ( SELECT uid, count( question_id ) AS question_cnt1 FROM practice_record GROUP BY uid order by question_cnt1 desc) AS b ON b.uid = c.uid
WHERE
a.uid = c.uid
order by exam_cnt asc
执行结果如下:
3.每个6/7级用户活跃情况
现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):
id | uid | nick_name | achievement | level | job | register_time |
1 | 1001 | 牛客1号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
2 | 1002 | 牛客2号 | 2300 | 7 | 算法 | 2020-01-01 10:00:00 |
3 | 1003 | 牛客3号 | 2500 | 7 | 算法 | 2020-01-01 10:00:00 |
4 | 1004 | 牛客4号 | 1200 | 5 | 算法 | 2020-01-01 10:00:00 |
5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
6 | 1006 | 牛客6号 | 2600 | 7 | C++ | 2020-01-01 10:00:00 |
试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
id | exam_id | tag | difficulty | duration | release_time |
1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
2 | 9002 | C++ | easy | 60 | 2021-09-01 06:00:00 |
3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
uid | exam_id | start_time | submit_time | score |
1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
1005 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:59 | 84 |
1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 81 |
1002 | 9001 | 2020-09-01 13:01:01 | 2020-09-01 13:41:01 | 81 |
1005 | 9001 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
题目练习记录表practice_record(uid用户ID, question_id题目ID, submit_time提交时间, score得分):
uid | question_id | submit_time | score |
1001 | 8001 | 2021-08-02 11:41:01 | 60 |
1004 | 8001 | 2021-08-02 19:38:01 | 70 |
1004 | 8002 | 2021-08-02 19:48:01 | 90 |
1001 | 8002 | 2021-08-02 19:38:01 | 70 |
1004 | 8002 | 2021-08-02 19:48:01 | 90 |
1006 | 8002 | 2021-08-04 19:58:01 | 94 |
1006 | 8003 | 2021-08-03 19:38:01 | 70 |
1006 | 8003 | 2021-08-02 19:48:01 | 90 |
1006 | 8003 | 2020-08-01 19:38:01 | 80 |
请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数,按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下:
uid | act_month_total | act_days_2021 | act_days_2021_exam | act_days_2021_question |
1006 | 3 | 4 | 1 | 3 |
1001 | 2 | 2 | 1 | 1 |
1005 | 1 | 1 | 1 | 0 |
1002 | 1 | 0 | 0 | 0 |
1003 | 0 | 0 | 0 | 0 |
解释:6/7级用户共有5个,其中1006在202109、202108、202008共3个月活跃过,2021年活跃的日期有20210907、20210804、20210803、20210802共4天,2021年在试卷作答区20210907活跃1天,在题目练习区活跃了3天。
我的答案如下(经过Mysql数据库验证,欢迎大家交流):
select user_info.uid as uid,
count(distinct act_month) as act_month_total,
count(distinct case when year(act_days)='2021' then act_days end) as act_days_2021,
count(distinct case when year(act_days)='2021' and a='exam' then act_days end) as act_days_2021_exam,
count(distinct case when year(act_days)='2021' and a='practice' then act_days end) as act_days_2021_practice
from user_info
left join
(select uid,date_format(start_time,'%Y%m') as act_month,date_format(start_time,'%Y%m%d') as act_days,'exam' as a from exam_record
union
select uid,date_format(submit_time,'%Y%m') as act_month,date_format(submit_time,'%Y%m%d') as act_days,'practice' as a from practice_record) as b on b.uid = user_info.uid
where level >= 6
group by uid
order by act_month_total desc,act_days_2021 desc
运行数据库,看一下结果: