SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table record(sid  string, cid  string, quarter  string, year  integer, grade integer)
insert into record values
(1, 0, 'w', 2020, 12), (1, 0, 'w', 2019, 10), (1, 0, 's', 2020, 30), (1, 0, 's', 2020, 32),
(2, 0, 'w', 2020, 100), (2, 0, 'w', 2019, 10), (2, 0, 's', 2020, 10), (2, 0, 'f', 2020, 50)
SELECT * FROM record ORDER BY sid, year DESC, quarter


sid
cid
quarter
year
grade


1
0
s
2020
30

1
0
s
2020
32

1
0
w
2020
12

1
0
w
2019
10

2
0
f
2020
50

2
0
s
2020
10

2
0
w
2020
100

2
0
w
2019
10


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


sid
year
quarter
gpa


1
2020
s
31

2
2020
f
50


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


sid
year
quarter
gpa


1
2020
s
31

2
2020
f
50


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


sid
year
quarter
gpa


1
2020
s
31

2
2020
f
50

sid	quarter	year	grade
1	s	2020	30
1	s	2020	32
1	w	2020	12
1	w	2019	10
2	f	2020	50
2	s	2020	10
2	w	2020	100
2	w	2019	10