Откровенная аналитика: “Задача с собеса которую не смогли решить в LinkedIn Недавно увидел в л…”

Задача с собеса которую не смогли решить в LinkedIn

Недавно увидел в линке разбор хитрой SQL-задачи про подсчёт количества общих друзей. Но решение было предложено не совсем правильное, поэтому захотел разобрать её сам

Задача такая

Есть таблица friends_table c колонками user, friend (это айдишники)
Таблица содержит пары пользователей социальной сети, которые состоят в друзьях друг у друга

Таблица устроена так, что user<friend (смысл этого условия в том, что каждая пара пользователей записана всего один раз)

Для каждой пары пользователей вернуть число общих друзей

Решение

WITH AllFriendPairs AS (
    SELECT user, friend FROM friends_table
    UNION ALL
    SELECT friend, user FROM friends_table
)
-- Находим общих друзей для каждой исходной пары
SELECT
    f1.user,
    f1.friend,
    COUNT(t2.friend) AS common_friends_count
FROM
    friends_table AS f1
JOIN
    AllFriendPairs AS t1 ON f1.user = t1.user
JOIN
    AllFriendPairs AS t2 ON f1.friend = t2.user AND t1.friend = t2.friend
GROUP BY
    f1.user, f1.friend
ORDER BY
    common_friends_count DESC;

Оригинальный пост, в котором подсмотрел задачу тут