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

分别满足两个活动的人

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


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

全部评论

相关推荐

双非阴暗爬行:我来看看笑死我了,这名字取得好想笑(没有不好的意思)
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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