update scores as a
set passed = TRUE
from
(
    select
        student_code
        ,subject_code
        ,score
        ,avg(score) over(partition by subject_code) avg_sub_score
    from
        scores
) as b
where
    a.student_code = b.student_code
    and a.subject_code = b.subject_code
    and a.score >= b.avg_sub_score
;
update scores set passed = FALSE where passed is null;

--select * from scores;