Get pairs of two records with highest average grade

Question

I want to calculate the average grade of every record, and then get the two records with the highest average grade, per category.

My grade table looks like this:

userid | recordid | grade
123    | 1        | 8
123    | 2        | 1
123    | 3        | 3
123    | 4        | 6
121    | 1        | 3
121    | 2        | 7
121    | 3        | 1
121    | 4        | 8
124    | 1        | 6
124    | 2        | 8
124    | 3        | 9
124    | 4        | 5

And the record table like this:

id | userid | name | category | year
1  | 101    | Foo  | FooCat   | 2021
2  | 102    | Bar  | FooCat   | 2021
3  | 103    | Foos | BarCat   | 2021
4  | 104    | Bars | BarCat   | 2021

The result will look like this:

id | name | category | grade_avg
4  | Bars | BarCat   | 6.3
1  | Foo  | FooCat   | 5.7
2  | Bar  | FooCat   | 5.3
3  | Foos | BarCat   | 4.3

I'm currently using this SQL query:

SELECT S.id
     , S.name
     , S.category
     , AVG(IB.grade) AS grade_avg
  FROM 
     (SELECT id
           , name
           , category
           , year
           , row_number() over (partition by category) as r 
        FROM records) S
    JOIN grades IB 
        ON IB.recordid = S.id
 WHERE S.r < 3 
   AND S.year=2021
 GROUP 
    BY IB.recordid
 ORDER 
    BY grade_avg DESC

The grouping does work perfectly, but the grades at partitioning are not sorted by their average grade, so you just get the first 2 records per category.

What is the best query to get the result I want to achieve? This is an example, I will translate it to my own use, so that I learn to use the given SQL example better.

Answer 1

I fixed this issue by taking the answer of Gordon Linoff as my base, and change some things in it.

This is working for me:

SELECT name, category, avg(grade) AS grade_avg
FROM (SELECT name, category, avg(grade) AS grade_avg,
         row_number() over (partition by category ORDER BY grade_avg DESC) as r 
      FROM grades IB JOIN
            records I 
            ON IB.recordid = I.id 
      WHERE year = 2021
      GROUP BY id
   ) S 
WHERE S.r < 3 
ORDER BY grade_avg DESC

So I'm partitioning on the category (ordered by the average grade), grouping by the ID, and then ordering by the average grade.