Find all elements that appear together in specific contexts
I have a database of restaurant reviews. I have three tables: restaurant
, user
and review
.
A restaurant
is N reviews
, and a user
is N reviews
. Thus, it review
contains foreign keys restaurant_id
and author_id
. Each table has an attribute ID
.
I would like to find users who post a lot. Specifically, I want to have a table with columns user_A, user_B, reviewed_together
where user_A
and user_B
are two user.IDs
and reviewed_together
contains the account of all restaurants that have a BOTH user_A
and overview user_B
.
I am new to SQL and I would like to learn how to handle these situations correctly, so links to resources and a sketch to your thought process would be a huge help :-)
source to share
you can use this query:
select r1.author_id as user_a, r2.author_id as user_b,
count(r1.restaurant_id) as reviewed_together
from review r1 inner join review r2 on r1.restaurant_id = r2.restaurant_id
and r1.author_id > r2.author_id
group by r1.author_id, r2.author_id
but be careful with
r1.author_id > r2.author_id
because if you change this to <> (unevenly) you end up with duplicate entries.
try it and tell me if this query will get the results you want.
source to share
Actually this answer may not be entirely correct, but I cannot use comments, so I will use the answer for answering, cleaning, etc.
My first logical test is the code below, it seems fine, but where is the condition. I'm not sure about this, I can improve it if you comment. Could you please try the code below, I could not try as I have no data.
select UserA.UserID as USER_A, UserB.UserID as USER_B, count(restID) as COMMON_COMMENTS
from
(select distinct(userID),restID from reviews group by restID) UserA,
(select distinct(userID),restID from reviews group by restID) UserB
where UserA.restID = UserB.restID
group by UserA.userID, userB.userID
Your comments will strengthen this request, hopefully.
source to share