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

with table1 as (
    select uid, score , 
    timestampdiff(minute, start_time , submit_time ) as timediff , difficulty,duration
    from exam_record left join examination_info using(exam_id)
    where year(submit_time) = 2021
),
# activity1
table2 as (
    select uid , min(score) , 'activity1' as activity
    from table1
    group by uid ,activity
    having min(score) >= 85
),
# activity2
table3 as (
    select uid,max(score), 'activity2' as activity 
    from table1
    where difficulty = 'hard' and timediff < 0.5 * duration  # 难度为hard 且 一半时间
    group by uid, activity
    having max(score) >= 80
)
select uid,activity
from table2

union all 
 
select uid,activity
from table3
subquery
order by uid, activity