题解 | #分别满足两个活动的人#
分别满足两个活动的人
https://www.nowcoder.com/practice/a126cea91d7045e399b8ecdcadfb326f
select
uid,
'activity1' as activity
from
exam_record as t1
inner join examination_info as t2 on t1.exam_id = t2.exam_id
where
year (t1.start_time) = 2021
group by
uid
having
min(t1.score) >= 85
union
select
uid,
'activity2' as activity
from
exam_record as t1
inner join examination_info as t2 on t1.exam_id = t2.exam_id
where
year (t1.start_time) = 2021
and t2.difficulty = 'hard'
and t1.score > 80
and timestampdiff (second, t1.start_time, t1.submit_time) <= (t2.duration * 60) / 2
group by
uid
order by uid;
