题解 | 分别满足两个活动的人

with t1 as (select difficulty,uid,score,duration,timestampdiff(second,start_time,submit_time)/60 as finish_time from examination_info ei
join exam_record er on ei.exam_id = er.exam_id),

t2 as (select t1.uid as uid,'activity1' as activity from t1 
left join (select uid from exam_record er
where score < 85) as non_activity_1
on t1.uid = non_activity_1.uid
where non_activity_1.uid is null
group by uid)

select uid, 'activity2' as activity from t1
where finish_time <= duration/2 and score > 80 and difficulty = 'hard'
group by uid

union

select uid,activity from t2
order by uid,activity