Skip to content

3058. Friends With No Mutual Friends 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
  TwoWayFriends AS (
    SELECT user_id1 AS user_id, user_id2 AS friend_id FROM Friends
    UNION ALL
    SELECT user_id2, user_id1 FROM Friends
  ),
  UserToMutualFriend AS (
    SELECT
      User1.user_id,
      User2.user_id AS friend_id
    FROM TwoWayFriends AS User1
    INNER JOIN TwoWayFriends AS User2
      USING (friend_id)
    WHERE User1.user_id != User2.user_id
  )
SELECT Friends.* FROM Friends
LEFT JOIN UserToMutualFriend
  ON (
    Friends.user_id1 = UserToMutualFriend.user_id
    AND Friends.user_id2 = UserToMutualFriend.friend_id)
WHERE UserToMutualFriend.friend_id IS NULL
ORDER BY 1, 2;