Whom to follow?

I have a website that have same idea of Twitter’s follow feature

one table has the relationship between users named user_followers as follows:

user_id follower_id
1---------2
1---------3
1---------4
2---------1
2---------4
2---------5
2---------6
3---------4
3---------5
3---------6

Now can I retrieve a list of users to suggest for a specific user like “Who to follow” in Twitter

sure you can, i can help you with the sql

but first, please give instructions on how it’s supposed to work

It’s supposed to suggest the most followed users (say top 10) by my followers

for example if I am a user A, and my followers are B and C

B is following D and E
C is following D and F

In this case, user D should be in the top of suggestions list for user A.

In other words, who are my followers also following?
Or do you mean, who are the ones I’m following following?

In any case this could grow exponentially in complexity, I’m curious to see r937’s query.

SELECT my_followers_following.user_id
  FROM user_followers AS my_followers
INNER
  JOIN user_followers AS my_followers_following
    ON my_followers_following.follower_id = my_followers.follower_id
   AND my_followers_following.user_id <> my_followers.user_id
 WHERE my_followers.user_id = 1   
GROUP
    BY my_followers_following.user_id
ORDER
    BY COUNT(*)

i tested it for you, on the data you posted, using “i am user 1” instead of “i am user A”, and it returns 2 and 3 (1 occurrence each)