SQL: Having multiple conditional Averages between two tables

I have two tables

Sample from e.g. T1

----------------------
ID  || Operation Name |
----------------------|
101 || QA             |
----------------------

Sample from T 2

----------------------------------------
ID  || Employee  ID || Employee || Audit |
----------------------------------------
101 || 505788       || Marley   || 50    |
230 || 5487525      || Smith    || 100   |
-----------------------------------------

I should return each operation name with it’s own Audit average in conditions (if it’s >= 90 as “Good” – if it’s <90 and >= 40 as “Medium” – if it’s < 40 as “Bad”) and prevent any audit with average (0)

the table should be like this

---------------------------------------------------
Operation Name || Operation ID || Audit Evaluation |
----------------------------------------------------
Maintenance    ||   190        ||       Medium     |
---------------------------------------------------

Answer

demo:db<>fiddle

SELECT
    t1.name AS operation_name,
    t1.id AS operation_id,
    CASE 
        WHEN AVG(t2.audit) >= 90 THEN 'Good'
        WHEN AVG(t2.audit) >= 40 THEN 'Medium'
        ELSE 'Bad'
    END AS audit_evaluation
FROM
    t1
JOIN
    t2 ON t1.id = t2.id
GROUP BY t1.id, t1.name
HAVING AVG(t2.audit) > 0

1. Joining both tables on id
2. Grouping by their ids
3. HAVING filters out the AVG = 0 groups
4. Calculating AVG
5. Using CASE clause to build the sections