WITH
Calc_avg
(
    test_code
    ,subject_code
    ,T_SCORE
    ,C_SUB
    ,AVG
)
AS
(
    SELECT
        test_code
        ,subject_code
        ,SUM(score) AS T_SCORE
        ,COUNT(*)  AS C_SUB
        ,SUM(score) / COUNT(*)  AS AVG
    FROM scores
    GROUP BY
         test_code
        ,subject_code
),
Calc_exam
(
    test_code
    ,student_code
    ,subject_code
    ,score
    ,ranking
    ,passed
)
AS
(
    SELECT
         SC.test_code
        ,SC.student_code
        ,SC.subject_code
        ,SC.score
        ,SC.ranking
        ,CASE WHEN score >= CA.AVG THEN TRUE ELSE FALSE END AS passed
    FROM scores AS SC
    LEFT JOIN Calc_avg AS CA ON SC.test_code = CA.test_code AND SC.subject_code = CA.subject_code
)

SELECT * FROM Calc_exam;