sql - SQLlite getting min and max and average using nested queries

Question

I have these tables

record: sid (string), cid (string), quarter (string), year (integer), grade(integer)
student: sid (string)

For every student who has taken at least one class, meaning a student is entered in the record table at least once, i need to get their GPA in the most recent quarter they were enrolled in. I need to display sid, quarter, year, and grade (gpa).

There are 3 quarters in a given calendar year, and it may be helpful to observe the order of the occurrence of quarters is in reverse alphabetical order ('W' > 'S' > 'F'). These stands for winter, spring, fall respectively. Fall being the latest quarter of the year.

this is what i came up with:

select sid, quarter, year, avg(grade) as gpa
from (select sid, min(quarter) as quarter, year, avg(grade) as grade
    from (select *, max(year) as maxy
        from record
        group by sid)
    group by sid)
group by sid;

this gives me the average grade for all quarters/years enrolled, and doesn't give me the latest quarter either.

I can only use functions such as NOT EXIST / EXIST, NOT IN/IN , group by, order by. I cannot use rank().

I was told that I should use NOT EXIST to get the latest quarter since the most recent quarter means for a specific quarter, there is no succeeding quarter.

any help would be greatly appreciated. thank you!

question from:https://stackoverflow.com/questions/65895954/sqllite-getting-min-and-max-and-average-using-nested-queries

Answer 1

You want solution using not exists? Here you go.

Select t.*
  From record t
 Where not exists
       (Select 1 from record tt
         Where tt.year > t.year
           And tt.quarter < t.quarter
           And tt.sid = t.sid)

Above query will give you all the data of student for latest quarter, then you can use the aggregate function according to your requirement.