calculating the average of marks from the beginning to this record

Question

i have a table named test with the below structure like this

id mark join_id
1  5    1
2  4    1
3  9    1
4  5    2 
5  7    2
6  12   2

i want to write a query that can get me the average of the marks from the beginning record to this record with the desired result as below

id mark join_id   avg_of_previous_marks
1  5    1         5      
2  4    1         4.5
3  9    1         6
4  5    2         5.75
5  7    2         6
6  12   2         7

i wrote this query but it doesn't seem to work correctly

SELECT test.id, test.mark, test.join_id,  test_avg.avg_of_previous_marks FROM test
LEFT JOIN (SELECT id, join_id, AVG(mark) as avg_of_previous_marks FROM test GROUP BY 
join_id) test_avg 
ON test_avg.join_id = test.join_id AND test_avg.id <= test.id

and it gives this resault

id mark join_id   avg_of_previous_marks
1  5    1         6      
2  4    1         6
3  9    1         6
4  5    2         8
5  7    2         8
6  12   2         8

Answer 1

Its a simple running total that you need.

select id,mark,join_id, avg(mark) over (order by id) avg_of_previous_marks from test_avg ;

fiddle here