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

with
    temp as (
        select
            exam_id,
            uid,
            timestampdiff(second, start_time, submit_time) / 60 as spend_time,
            # (submit_time - start_time) / 60 as spend_time,
            score,
            tag,
            difficulty,
            duration
        from
            exam_record
            join examination_info using (exam_id)
        where
            year(start_time) = 2021
    )
select distinct
    uid,
    "activity1" as activity
from
    temp
where
    score >= 85
union all
select distinct
    distinct uid,
    "activity2" as activity
from
    temp
where
    spend_time <= 0.5 * duration
    and difficulty = 'hard'
    and score > 80

    order by uid