How to get the values for every group of the top 3 types

user_4995249 0
Viewed 76

I've got this table ratings:

id user_id type value
0 0 Rest 4
1 0 Bar 3
2 0 Cine 2
3 0 Cafe 1
4 1 Rest 4
5 1 Bar 3
6 1 Cine 2
7 1 Cafe 5
8 2 Rest 4
9 2 Bar 3
10 3 Cine 2
11 3 Cafe 5

I want to have a table with a row for every pair (user_id, type) for the top 3 rated types through all users (ranked by sum(value) across the whole table).

Desired result:

user_id type value
0 Rest 4
0 Cafe 1
0 Bar 3
1 Rest 4
1 Cafe 5
1 Bar 3
2 Rest 4
3 Cafe 5
2 Bar 3

I was able to do this with two queries, one to get the top 3 and then another to get the rows where the type matches the top 3 types.

Does someone know how to fit this into a single query?

greatest-n-per-group aggregate postgresql sql
2 Answers

Get rows per user for the 3 highest ranking types, where types are ranked by the total sum of their value across the whole table.

So it's not exactly about the top 3 types per user, but about the top 3 types overall. Not all users will have rows for the top 3 types, even if there would be 3 or more types for the user.

Strategy:
Aggregate to get summed values per type (type_rnk).
Take only the top 3. (Break ties ...)
Join back to main table, eliminating any other types.
Order result by user_id, type_rnk DESC

SELECT r.user_id, r.type, r.value
FROM   ratings r
JOIN  (
   SELECT type, sum(value) AS type_rnk
   FROM   ratings
   GROUP  BY 1
   ORDER  BY type_rnk DESC, type  -- tiebreaker
   LIMIT  3   -- strictly the top 3
   ) v USING (type)
ORDER  BY user_id, type_rnk DESC;

db<>fiddle here

Since multiple types can have the same ranking, I added type to the sort order to break ties alphabetically by their name (as you did not specify otherwise).

Turns out, we don't need window functions - the ones with OVER and, optionally, PARTITION for this. (Since you asked in a comment).

user_9398600

I think you just want row_number(). Based on your results, you seem to want three rows per type, with the highest value:

select t.*
from (select t.*,
             row_number() over (partition by type order by value desc) as seqnum
      from t
     ) t
where seqnum <= 3;

Your description suggests that you might just want this per user, which is a slight tweak:

select t.*
from (select t.*,
             row_number() over (partition by user order by value desc) as seqnum
      from t
     ) t
where seqnum <= 3;
user_11440350
Related