Is it possible to make such a sql query to find 2 users with the maximum number of common groups (as in VK)?

Question

MySQL

Hello.
There is a user table:
id
name
There is a group table (like VK) in which the user is a member:
id
group_name
There is a junction table in which users and the groups they are members are stored :
user_id
group_id
which is the maximum number of common groups?
UPD:
thanks for the replies, but please take a look at this demo:
sqlfiddle
should output Alice and Bob. Users with the maximum number of GENERAL groups
Thanks in advance

Answer 1

SELECT `u1`.`name`, `u2`.`name`, COUNT(*) AS `count`
    FROM `users_groups` AS `ug1` 
    JOIN `users_groups` AS `ug2` 
        ON `ug2`.`group_id` = `ug1`.`group_id`
            AND `ug2`.`user_id` > `ug1`.`user_id`
    JOIN `users` AS `u1`
        ON `u1`.`id` = `ug1`.`user_id`
    JOIN `users` AS `u2`
        ON `u2`.`id` = `ug2`.`user_id`
    GROUP BY `ug1`.`user_id`, `ug2`.`user_id`
    ORDER BY `count` DESC
    LIMIT 1

Answer 2

SELECT user.id, count(*) as  numbers_of_group
    FROM user 
        JOIN user_group
            ON user_group.user_id =  user.id
GROUP BU user.id
ORDER BY numbers_of_group DESC
LIMIT 2

Nice but not fast

Answer 3

DEMO .

SELECT
  u.name,
  COUNT(ug.user_id) AS groups
FROM
  users u
LEFT JOIN
  users_groups ug ON
    u.id = ug.user_id
GROUP BY
  u.name
ORDER BY
  groups DESC
LIMIT 2;

Answer 4

SELECT user1, user2, cnt
FROM
(
    SELECT t3.name AS user1,
                 t4.name AS user2, 
                 COUNT(*) AS cnt
    FROM users_groups AS t1
    INNER JOIN users_groups AS t2
    ON t1.user_id<t2.user_id 
    AND t1.group_id=t2.group_id
    LEFT JOIN users AS t3
    ON t1.user_id=t3.id
    LEFT JOIN users AS t4
    ON t2.user_id=t4.id
    GROUP BY t1.user_id, t2.user_id, t3.name, t4.name
) AS t1
ORDER BY cnt DESC
LIMIT 0,1

By the way, the task is not quite complete, because there can be several such pairs