I want to calculate the average grade of every record, and then get the two records with the highest average grade, per category.
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
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?
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.