How to find maximum of average grouped by in sql I have three tables Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark}

And i would select top student in each faculty

I'm tired to perform this in multiple ways but i can't

SELECT Faculty.name, Student.name, MAX(AVG(mark)) 
FROM (Faculty inner join Student On Faculty.id = Student.faculty_id) inner join Course_student On Student.id = Course_student.student_id
group by Faculty.name
I cannot check the results but I hope that the query below should work.
In future, please, add the DDL (Data Definition Language) including the scripts for table creation and also to insert a few records that will ilustrate the expected result.

WITH CTE AS (
SELECT Faculty.name AS Faculty_Name, 
       Student.name AS Student_Name, 
       AVG(mark) AS Avg_Mark,  
       ROW_NUMBER() OVER(PARTITION BY Faculty.name ORDER BY AVG(mark) DESC) AS Row
FROM Faculty 
INNER JOIN Student On Faculty.id = Student.faculty_id) 
INNER JOIN Course_student On Student.id = Course_student.student_id
GROUP BY Faculty.name
)
SELECT Faculty_Name, Student_Name, Avg_Mark
FROM CTE
WHERE Row = 1