Question 168874 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL: How to find user w/most friends (incl. duplicates)?

This question is related to this previous question of mine:

I have a table "Friendship", which looks like this:

CREATE TABLE Friendship (
    User1 INT,
    User2 INT,
    PRIMARY KEY (User1, User2),
    CONSTRAINT columns_cannot_equal CHECK (User1 <> User2)
);

Here is some sample data:

user_id,friend_id
134443,5888
232309,8909
232309,185595
232309,172634
232309,118037
80195,46650
80195,80429
80195,216751
80195,224625
175987,175483
175987,243131
175987,141773
175987,226862
247382,68616
247382,183025
101421,91108
101421,159948
101421,12395
101421,55391
201263,104651
201263,175709
201263,243725
201263,141645
241625,266922
241625,184460
241625,103497
241625,16108
4860,175056
4860,103123
4860,184967

I want to find the user with the most friends.

From a previous question I already have the following query, credit to Maninder.

SELECT user_id, COUNT(*) AS friend_count
FROM (
    SELECT User1 AS user_id
    FROM Friendship
    UNION ALL
    SELECT User2 AS user_id
    FROM Friendship
) AS all_users
GROUP BY user_id
ORDER BY friend_count DESC
LIMIT 1;

Could you please tell me how to allow duplicates in this query; i.e. if multiple users the highest number of friends, say 20, I want to get all of the users with 20 friends.

EDIT: To clarify my question: Say the maximum number of friends per user from a given sample is 20 and the users with user_ids "232309", "4860" and "1717" have 20 friends each. Then my query should return:

232309
4860
1717

EDIT 2: I have now come up with the following code, but I am unable to get the user_id from there.

SELECT MAX(Friend_Count)
FROM (
    SELECT user_id, COUNT(*) AS friend_count
    FROM (
        SELECT User1 AS user_id
        FROM Friendship
        UNION ALL
        SELECT User2 AS user_id
        FROM Friendship
    ) AS all_users
    GROUP BY user_id
    ORDER BY Friend_Count DESC
) as Friend_Count_Table
Tags:

2

Answers


  1. 0

    You can do it using the window function rank() then select only the ones with rank = 1 :

    With cte as (
  SELECT user_id, COUNT(*) AS friend_count
  FROM (
    SELECT User1 AS user_id
    FROM Friendship
    UNION ALL
    SELECT User2 AS user_id
    FROM Friendship
  ) AS all_users
  GROUP BY user_id
  ORDER BY friend_count DESC
),
cte2 as (
  select user_id, rank() over( order by friend_count desc ) as rn
  from cte
)
select usert_id
from cte2
where rn = 1
    Login or Signup to reply.
  2. 0

    Postgres15 has with ties clause.

    select   user_id
        ,count(*)
from     t
group by user_id
order by count(*) desc
fetch    first 1 rows with ties
    user_id count
    80195 4
    232309 4
    175987 4
    101421 4
    241625 4
    201263 4

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search