Взаимные подписки (mutual follows)

35. Найти пользователей с взаимной подпиской

Условие задачи:
Дана таблица подписок пользователей:

user_id | follower_id
--------+------------
1       | 5
1       | 4
2       | 3
3       | 2
5       | 1

Необходимо найти пары пользователей, которые подписаны друг на друга.

Каждую пару нужно вывести только один раз.

Спойлеры к решению

Подсказки

💡 Соедини таблицу саму с собой.
💡 Для взаимной подписки должна существовать обратная запись.
💡 Условие будет выглядеть как a.user_id = b.follower_id и a.follower_id = b.user_id.
💡 Чтобы не получать одновременно (1, 5) и (5, 1), можно оставить только строки, где user_id < follower_id.

Решение

Вариант через self join:

SELECT DISTINCT
    f1.user_id,
    f1.follower_id
FROM follows f1
JOIN follows f2
    ON f1.user_id = f2.follower_id
   AND f1.follower_id = f2.user_id
WHERE f1.user_id < f1.follower_id;

Для исходных данных результат будет:

user_id | follower_id
--------+------------
1       | 5
2       | 3

Условие соединения проверяет наличие обратной подписки:

f1.user_id = f2.follower_id
AND f1.follower_id = f2.user_id

Условие:

f1.user_id < f1.follower_id

оставляет каждую взаимную пару только в одном направлении.

Альтернативный вариант через EXISTS:

SELECT DISTINCT
    f1.user_id,
    f1.follower_id
FROM follows f1
WHERE f1.user_id < f1.follower_id
  AND EXISTS (
      SELECT 1
      FROM follows f2
      WHERE f2.user_id = f1.follower_id
        AND f2.follower_id = f1.user_id
  );

DISTINCT полезен, если таблица потенциально может содержать повторяющиеся записи о подписках.