Assuming a reasonable schema definition (please share the actual schema definition with column names and types in the question, and do not use an image to do it), you want something like this:

SELECT s.ID, s.Name, s.[Group], s.Location, AVG(g.Grade)
FROM 
( 
    SELECT Student, Subject, Grade
        ,row_number() over (partition by student, subject order by [ID] desc) rn
    FROM Grades
) g
INNER JOIN Students s ON s.Id = g.Student
WHERE rn = 1
GROUP BY s.ID, s.Name, s.[Group], s.Location

This uses a windowing function to partition and then number rows per student/subject based on the date, so you can then limit to only the first row within each partition and take the average from there. It should tend to perform better than solution which need to JOIN to a projection selecting by the MAX(date) .

Note I said "reasonable schema" at the beginning of the answer. There is a concern based on the image (which again: it gets squished in the view and is nearly unreadable — don't post data that way!) that multiple grades for a subject are stored in the same column as comma-separated data. This is what we call an unreasonable schema. Storing comma-separated data in a single column is considered a BROKEN schema design: something that needs to be fixed.

Another important thing to understand is the use of the Date column, which was not shown in the question. SQL databases explicitly disclaim any responsibility for preserving any kind of natural order for your records. There is no such thing as insert order, table order, or disk order, and there are a number of things that can cause a database to reshuffle records on disk or return rows in a different order, even for consecutive runs of the same query.

Therefore, you MUST have a way to define "last grade" in terms of actual data in the rows. Even a sequentially assigned ID would be enough, but we need something. Otherwise, you will not be able to write this query! What is it that makes History(7) be last rather than History(5) ?

You can use the following SQL query to calculate the general average of the latest grades for each subject for each student:

SELECT s.Id, s.Name, g.Subject, AVG(g.Grade) AS Average
FROM Students s
JOIN Grades g ON s.Id = g.Student
JOIN (
  SELECT Student, Subject, MAX(Date) AS LatestDate
  FROM Grades
  GROUP BY Student, Subject
) latest_grades ON g.Student = latest_grades.Student
  AND g.Subject = latest_grades.Subject
  AND g.Date = latest_grades.LatestDate
GROUP BY s.Id, s.Name, g.Subject;

This query joins the Students and Grades tables based on the foreign key relationship. It then joins with a subquery that selects the latest grade for each student-subject pair based on the maximum date. Finally, it calculates the average of the latest grades for each subject for each student using the AVG aggregate function, and groups the results by student and subject.

use the MAX() function to get the most recent grade for each subject. You can then calculate the average of these grades using the AVG() function.

Here is an example:

SELECT Students.Name, AVG(Grades.Grade)
FROM Students
JOIN (
    SELECT Student, Subject, MAX(Date) AS LatestDate
    FROM Grades
    GROUP BY Student, Subject
) AS LatestGrades ON Students.Id = LatestGrades.Student
JOIN Grades ON LatestGrades.Student = Grades.Student
    AND LatestGrades.Subject = Grades.Subject
    AND LatestGrades.LatestDate = Grades.Date
GROUP BY Students.Name

I think you have to create a new table first that has the LatestGrades and then create the JOIN. Looks like You don’t even need the subj column in the final output, just an avg score across all subjects for every student.

WITH
Newtable AS 
(SELECT id, subject,grade, MAX(date) as LatestDate
FROM Grades
GROUP BY student, subject)
SELECT Students.id, students.name, AVG (newtable.grade) AS Avggrade
FROM (Newtable FULLJOIN Grades
ON Newtable.id=Grades.id) 
FULL JOIN Students ON students.id=grades.id