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

分别满足两个活动的人

https://www.nowcoder.com/practice/a126cea91d7045e399b8ecdcadfb326f

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;

全部评论

相关推荐

评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务