CODEWITHSUNDEEP
sqlalgorithmpostgresql
Nick
Nick

Reputation: 95

How to query average difference in ratings between a user and all other users

I have a Postgres table of book ratings with the following structure:

id serial primary key, userid integer not null, bookid integer not null, rating integer not null

I want to extract the average difference between a given user's ratings and and every other user's ratings, each considered separately. Put another way, as a user, I want to know what the average difference between my ratings and some x user's ratings, for all x. Ultimately, I want to be able to put this average number in a new SQL table alongside ids for the given user and the user being compared.

I'm pretty unfamilar with SQL past simple queries, and I've implemented a solution that dances back and forth between Javascript loops and SQL queries. I'm looking for something as clean as possible if anyone is willing to help.

EDIT: Here's a short example of data and ideal output.

id,userid,bookid,rating

1,1,1,5
2,1,2,2
3,1,3,3
4,1,4,3
5,1,5,1
6,2,1,5
7,2,2,2
8,3,1,1
9,3,2,5
10,3,3,3

Here is the ideal output, structured as another sql table:

id serial primary key,
currentuser integer not null,
compareuser integer not null,
averagediff float not null

id,currentuser,compareuser,averagediff

1,1,2,0
2,1,3,2.33333

Upvotes: 1

Views: 70

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with a self join.

select t1.userid as current_user,t2.userid as compare_user
,avg(abs(t1.rating-t2.rating)) as average_difference
from tbl t1
join tbl t2 on t1.userid<t2.userid and t1.bookid=t2.bookid
group by t1.userid,t2.userid

Upvotes: 1

Related Questions