【牛客】SQL133 分别满足两个活动的人

描述

为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到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