Calculate percentage of students SQL

How to calculate percentage of students with higher mark than average for each course?

Assume I have a table (avg_marks) with average marks for each course and number of students in the course:

course_id    avg_mark    num_students
12345        74          20
12346        70          17
12347        64          33
...

I also have a table (enrolments) with all courses, students enrolled in those courses and their mark for the course:

course_id    student     mark
12345        1010        63
12345        2111        75
12345        3221        85
12345        6788        40
...    
12347        8989        90
...

The expected output would be the table with course id and percentage of students with higher marks than average:

course_id    percentage
12345        40
12345        20
12346        50
...

I have calculated number of students who have higher mark than average, but somehow I wasn’t able to calculate the percentage (perhaps because the table contains all courses?). How can I modify this query or make a new one to calculate the percentage of students with higher mark than average?

Number of students with higher than average mark:

SELECT e.course_id, COUNT(e.student)
FROM enrolments e, avg_mark av
WHERE e.mark > av.avg_mark AND e.course_id=av.course_id

Output of the above query was like the following:

course_id     count 
12345         5
12346         10
12347         8
...

Answer

You don’t need the table avg_marks.
Use window function AVG() in enrolments to get the average mark for each course_id and then use conditional aggregation with AVG() aggregate function to get the percentage:

SELECT course_id, 
       ROUND(100 * AVG((mark > avg_mark)::int)) percentage 
FROM (SELECT *, AVG(mark) OVER (PARTITION BY course_id) avg_mark FROM enrolments) e 
GROUP BY course_id

See the demo.