Find the user with the most contact

Question

Users send messages to each others message boxes, how to find the user who knows the most other users (receive AND send??). I have SQL Server.

Table:

• USER_ID
• USER_NAME
• USER_GROUP
• MESSAGE_ID
• MESSAGE_MESSAGE
• MESSAGE_FROM_USER
• M_ESSAGE_TO_USER

A user can send and receive, so who knows the most other users (by send AND receive??)

Answer 1

SQL Server 2005+, Using CTE:

---

 WITH usr_list AS (
   SELECT x.user_id,
          x.message_from_user AS other_user
     FROM TABLE x
   UNION
   SELECT y.user_id,
          y.message_to_user AS other_user
     FROM TABLE y)
  SELECT TOP 1
         ul.user_id,
         COUNT(*) AS num_friends
    FROM usr_list ul
GROUP BY ul.user_id
ORDER BY num_friends DESC

Non CTE equivalent:

---

  SELECT TOP 1
         ul.user_id,
         COUNT(*) AS num_friends
    FROM (SELECT x.user_id,
                 x.message_from_user AS other_user
            FROM TABLE x
          UNION
          SELECT y.user_id,
                 y.message_to_user AS other_user
            FROM TABLE y) ul
GROUP BY ul.user_id
ORDER BY num_friends DESC

Answer 2

Untested, but this should ensure that each other user is only counted once, regardless if they were the sender or the recipient, or how many messages they sent or received:

SELECT TOP 1
  user_id,
  friend_count = COUNT(DISTINCT u)
FROM
(
  SELECT user_id,
    u = message_from_user
    FROM table
  UNION ALL
  SELECT user_id,
    u = message_to_user
    FROM table
) AS x
ORDER BY friend_count DESC;