写法一

avg+over 开窗，与group by相比，聚合开窗会保留原来的所有行，group by只会留下每个类别聚合后的一行，因此这里开窗有利于where语句的大小比较

select
    min(a.score) as min_score_over_avg
from
    (select
        ei.tag as tag, 
        er.score as score, 
        avg(score) over(partition by ei.tag) as mean_score
    from
        exam_record as er
    inner join
        examination_info as ei
    on
        er.exam_id = ei.exam_id
    where
        ei.tag = 'SQL') as a
where 
    a.score >= mean_score;

写法二

with a as
(select
        ei.tag as tag, 
        er.score as score
    from
        exam_record as er
    inner join
        examination_info as ei
    on
        er.exam_id = ei.exam_id
    where
        ei.tag = 'SQL')

select
    min(score) as min_score_over_avg
from
    a
where 
    score >= (select avg(score) from a);