描述
为了促进更多用户在牛客平台学习和刷题进步,我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动,分别给每次试卷得分都能到85分的人(activity1)、至少有一次用了一半时间就完成高难度试卷且分数大于80的人(activity2)发了福利券。
现在,需要你一次性将这两个活动满足的人筛选出来,交给运营同学。请写出一个SQL实现:输出2021年里,所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号,按用户ID排序输出。
现有试卷信息表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得分):
id | uid | exam_id | start_time | submit_time | score |
1 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
2 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 70 |
3 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
4 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 89 |
5 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
示例数据输出结果:
uid | activity |
1001 | activity2 |
1003 | activity1 |
1004 | activity1 |
1004 | activity2 |
解释:用户1001最小分数81不满足活动1,但29分59秒完成了60分钟长的试卷得分81,满足活动2;1003最小分数86满足活动1,完成时长都大于试卷时长的一半,不满足活动2;用户1004刚好用了一半时间(30分钟整)完成了试卷得分85,满足活动1和活动2。
代码1:
select uid,'activity1' as activity
from
(select uid,min(score) as min_score
from exam_record
where year(start_time)='2021'
group by uid)t
where min_score>=85
union
select uid,'activity2'
from
exam_record left join examination_info using(exam_id)
where 2*timestampdiff(minute,start_time,submit_time)<duration
and difficulty='hard' and score>80 and left(start_time,4)='2021'
order by uid
代码2:
select uid,'activity1' as activity
from
exam_record
where year(start_time)='2021'
group by uid
having min(score)>=85
union
select uid,'activity2'
from
exam_record left join examination_info using(exam_id)
where 2*timestampdiff(minute,start_time,submit_time)<duration
and difficulty='hard' and score>80 and left(start_time,4)='2021'
order by uid