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

select uid,'activity1' activity

from

(

select uid,any_value(score) score,any_value(time) time from (

select uid,score,TimeStampDiff(minute,start_time,submit_time)/duration time

from exam_record a left join examination_info b

on a.exam_id = b.exam_id

where year(submit_time) = '2021'

and submit_time is not null order by score limit 100) c

group by uid) d

where score >=85

union

select uid,'activity2' activity

from

(

select uid,any_value(score) score,any_value(time) time from (

select uid,score,TimeStampDiff(minute,start_time,submit_time)/duration time

from exam_record a left join examination_info b

on a.exam_id = b.exam_id

where year(submit_time) = '2021'

and difficulty = "hard"

and submit_time is not null order by score limit 100) c

group by uid

) e

where score >80 and time <0.5

order by uid;