sql - SQLlite trying to get the minimum value of an attribute after getting the maximum value of another attribute

Question

I have the ff schema:

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

Using SQLlite: 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.

My idea was to first get the max year for every student who has every taken a class, and from there get the min quarter for that year (the min gets the latest quarter for that school year), and then getting the avg of the grades within those results.

So it seems like I only need the records table. To get the most recent year they've been enrolled, I use the max function:

select *, max(year)
from record
group by sid;

This successfully gives me the latest year for every student that has ever taken a class.

Now when I try to get the latest quarter they were enrolled for that year, I use min(quarter) and then I try to use avg(grade) to get the gpa for that latest quarter they took but the results end up getting the average for all classes they took, and displays the min quarter regardless of year

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.

functions I can use are group by, order, by, aggregate functions (min, max, avg), not exist /exist, in/not in.

Any help would be greatly appreciated. Thank you!

question from:https://stackoverflow.com/questions/65893254/sqllite-trying-to-get-the-minimum-value-of-an-attribute-after-getting-the-maximu

Answer 1

You can use window function RANK() to filter only the rows of the most recent year/quarter and then aggregate to get the average grade:

SELECT sid, year, quarter, AVG(grade) gpa
FROM (
  SELECT *, RANK() OVER (PARTITION BY sid ORDER BY year DESC, quarter) rnk
  FROM record
)
WHERE rnk = 1
GROUP BY sid, year, quarter

Without window functions, with a correlated subquery:

SELECT r1.sid, r1.year, r1.quarter, AVG(r1.grade) gpa
FROM record r1
WHERE (r1.year, r1.quarter) = (
  SELECT r2.year, r2.quarter
  FROM record r2
  WHERE r2.sid = r1.sid
  ORDER BY r2.year DESC, r2.quarter
  LIMIT 1
)
GROUP BY r1.sid, r1.year, r1.quarter

Or with NOT EXISTS:

SELECT r1.sid, r1.year, r1.quarter, AVG(r1.grade) gpa
FROM record r1
WHERE NOT EXISTS (
  SELECT 1 FROM record r2
  WHERE r2.sid = r1.sid AND (r2.year > r1.year OR (r2.year = r1.year AND r2.quarter < r1.quarter))
)
GROUP BY r1.sid, r1.year, r1.quarter

See a simplified demo.