CODEWITHSUNDEEP
mysqlsql
devyoon
devyoon

Reputation: 69

SQL, How can i get a user who has most common friend with given user?

For example, there is a table RELATION which has 2 columns user1, user2.

If user 'A' and user 'B' are friend, only A,B or B,A record exists (no duplication).

So my question is, with table like below, how can i get a user who has most common friends with A ?
(In this case, answer is C, because A's friends are B,D,E,K and C's friends are B,D,K)

user1 user2
A B
A D
A E
B C
C D
C K
K A
K E

Upvotes: 0

Views: 182

Answers (3)

nayi224
nayi224

Reputation: 565

CREATE TABLE test (user1 CHAR(1), user2 CHAR(1));
INSERT INTO test VALUES
('A', 'B'),
('A', 'D'),
('A', 'E'),
('B', 'C'),
('C', 'D'),
('C', 'K'),
('K', 'A'),
('K', 'E');
SELECT * FROM test;

with tab1 as(
select t1.user1, t1.user2
  from test t1
 union all
select t1.user2, t1.user1
  from test t1
)
, tab2 as (
select concat(least(t1.user1, t2.user1), '-', greatest(t1.user1, t2.user1)) aa,
       count(*) cot
  from tab1 t1,
       tab1 t2
 where t1.user1 != t2.user1
   and t1.user2 = t2.user2
 group by 1
)
select distinct first_value(t1.aa) over(order by t1.cot desc) from tab2 t1
;

Upvotes: 1

alirezadp10
alirezadp10

Reputation: 183

SELECT person1,person2,count(*) as count FROM
(
  SELECT u1.user1 as person1, u1.user2 as common_friend, u2.user2 as person2 FROM users as u1 join users as u2 on u1.user2 = u2.user1 where person1 != person2
  union
  SELECT u1.user1 as person1, u1.user2 as common_friend, u2.user1 as person2 FROM users as u1 join users as u2 on u1.user2 = u2.user2 where person1 != person2
  union
  SELECT u1.user2 as person1, u1.user1 as common_friend, u2.user2 as person2 FROM users as u1 join users as u2 on u1.user1 = u2.user1 where person1 != person2
  union
  SELECT u1.user2 as person1, u1.user1 as common_friend, u2.user1 as person2 FROM users as u1 join users as u2 on u1.user1 = u2.user2 where person1 != person2
) as joined_users
group by person1,person2
order by count desc

preview

Upvotes: 0

Akina
Akina

Reputation: 42622

WITH
-- get 1st friend
cte1 AS ( SELECT user2
          FROM test
          WHERE user1 = 'A'
        UNION 
          SELECT user1
          FROM test
          WHERE user2 = 'A' ),
-- get 2nd friend
cte2 AS ( SELECT test.user2
          FROM test, cte1
          WHERE test.user1 = cte1.user2
        UNION 
          SELECT test.user1
          FROM test, cte1
          WHERE test.user2 = cte1.user2 )
-- remove self and get the most common user
SELECT user2
FROM cte2
WHERE user2 <> 'A'
GROUP BY user2
ORDER BY COUNT(*) DESC LIMIT 1

If there exists more than one user with the same intermediate friends amount then only one (random) of them will be returned. If all such users needed then use additional CTE with RANK().

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c54b3ebf551eca2355d7b8f23852088

Upvotes: 1

Related Questions