CODEWITHSUNDEEP
mysqlsql
GROVER.
GROVER.

Reputation: 4378

retrieving most followed user from a users followers

So I have a followers table which (obviously) contains which users follow who.

What I would like to do is find out all of User 1's followers (U1F), find out who they're all following (U1Ffol), and then using U1Ffol, sum together who U1F are following the most collectively. This may not make sense, but I've got a pretty rough idea how to show it in SQL (bear in mind I'm a bit of a noob, so apologies because this is clearly invalid code and isn't what I want to use).

/* retrieve user 1's followers */
SELECT follower AS U1F FROM followers WHERE following = 1;
/* retrieve who each of those users is following */
SELECT following AS U1Ffol FROM followers WHERE follower IN U1F;
/* find out who they're all following *collectively* */
SELECT 
  following AS User,
  COUNT(*) as f_count 
FROM U1Ffol 
GROUP BY User 
ORDER BY f_count DESC

Here is the table structure:

+----+----------+-----------+
| id | follower | following |
+----+----------+-----------+
|  1 |        2 |         1 |
|  2 |        5 |         1 |
|  3 |        9 |         1 |
|  4 |        1 |         4 |
|  5 |        1 |         5 |
|  6 |        4 |         1 |
+----+----------+-----------+

So, in this case, the expected output would be (funnily enough the person who they're following the most, collectively, is User 1):

+------+---------+
| User | f_count |
+------+---------+
|    1 |       4 |
|    4 |       1 |
|    5 |       1 |
+------+---------+

Hopefully this makes a bit more sense.

I've been trying to figure this SQL out for the last couple of days, but I'm too new to the language to understand how to implement it properly. Therefore, all help and tips would be appreciated,

Cheers.

Upvotes: 1

Views: 175

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your sample results seem to be:

SELECT following AS User,
       COUNT(*) as f_count 
FROM U1Ffol 
WHERE 1 IN (follower, following)
GROUP BY User ;

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Is below what you want -

SELECT following AS User,
       COUNT(*) as f_count 
FROM (SELECT following
      FROM followers
      WHERE follower IN (SELECT follower
                         FROM followers
                         WHERE following = 1)) t
GROUP BY following 
ORDER BY f_count DESC;

Upvotes: 1

Benoit F
Benoit F

Reputation: 499

I fixed your queries, after FROM, you need to put the name of your table containing the columns id | followers | etc

The result of a query is also a table, thats why im reusing these results in the next queries, its called sub queries 

  /* retrieve user 1's followers */
    SELECT follower AS U1F FROM NAME_OF_YOUR_TABLE WHERE following = 1;
    /* retrieve who each of those users is following */
    SELECT following AS U1Ffol FROM NAME_OF_YOUR_TABLE WHERE follower IN 
    (SELECT follower AS U1F FROM NAME_OF_YOUR_TABLE WHERE following = 1);
    /* find out who they're all following *collectively* */
    SELECT 
      following AS User,
      COUNT(*) as f_count 
    FROM (SELECT following AS U1Ffol FROM NAME_OF_YOUR_TABLE WHERE follower IN 
    (SELECT follower AS U1F FROM NAME_OF_YOUR_TABLE WHERE following = 1))
    GROUP BY User 
    ORDER BY f_count DESC

Upvotes: 0

Related Questions