Most Connected Member

On a neighbourhood social app, two members become friends when one sends a request and the other accepts. Each friendship is stored once in friend_links, and it counts for both members, whichever side they were on.

Find the member (or members) with the most friends. Return member_id and friend_count. If several members share the highest count, return all of them. Order by member_id ascending. If there are no friendships at all, return no rows.

Tables

friend_links — One row per friendship. A pair never appears twice, in either direction, and nobody befriends themselves.

Example

friend_links

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Subqueries & CTEs · problem 30 of 70 · SQLite

The idea

The table is undirected data stored in a directed shape: each friendship appears once, but belongs to both people. Turning it into one row per (friendship, member) fixes that: select requester_id, then UNION ALL select accepter_id. Now a member appears once for every friendship they are in, whichever side they were on.

UNION ALL matters: plain UNION removes duplicate rows, and since the stacked column holds only member ids, it collapses each member to a single row.

Group the stacked ids and count to get friend_count. To return every member tied for first place, compare each count with the maximum count (a scalar subquery over the counts CTE) rather than sorting and taking one row. The alternative ranks the counts with RANK() and keeps rank 1, which treats ties the same way. With no friendships, both the counts and the maximum are empty, so no rows come back.

Solution

WITH ends AS (
  SELECT requester_id AS member_id FROM friend_links
  UNION ALL
  SELECT accepter_id FROM friend_links
),
counts AS (
  SELECT member_id, COUNT(*) AS friend_count
  FROM ends
  GROUP BY member_id
)
SELECT member_id, friend_count
FROM counts
WHERE friend_count = (SELECT MAX(friend_count) FROM counts)
ORDER BY member_id;

Another way

WITH ends AS (
  SELECT requester_id AS member_id FROM friend_links
  UNION ALL
  SELECT accepter_id FROM friend_links
),
ranked AS (
  SELECT member_id,
         COUNT(*) AS friend_count,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM ends
  GROUP BY member_id
)
SELECT member_id, friend_count
FROM ranked
WHERE rnk = 1
ORDER BY member_id;

Why common answers fail

Counts only the requester side, so friendships a member accepted are ignored.

SELECT requester_id AS member_id, COUNT(*) AS friend_count
FROM friend_links
GROUP BY requester_id
HAVING COUNT(*) = (SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM friend_links GROUP BY requester_id))
ORDER BY member_id;

Uses UNION instead of UNION ALL, which removes duplicate member ids and leaves every member with a count of 1.

WITH ends AS (
  SELECT requester_id AS member_id FROM friend_links
  UNION
  SELECT accepter_id FROM friend_links
), counts AS (
  SELECT member_id, COUNT(*) AS friend_count FROM ends GROUP BY member_id
)
SELECT member_id, friend_count FROM counts
WHERE friend_count = (SELECT MAX(friend_count) FROM counts)
ORDER BY member_id;

Takes the top row with LIMIT 1, which silently drops members tied for the most friends.

WITH ends AS (
  SELECT requester_id AS member_id FROM friend_links
  UNION ALL
  SELECT accepter_id FROM friend_links
)
SELECT member_id, COUNT(*) AS friend_count
FROM ends
GROUP BY member_id
ORDER BY friend_count DESC, member_id
LIMIT 1;

What the interviewer is checking

This tests whether you notice that a pair table describes a symmetric relationship and whether you keep ties instead of reaching for LIMIT 1.

Mistakes to avoid

• Counting only one column of the pair.
• Using UNION, which deduplicates the stacked ids.
• Using ORDER BY ... LIMIT 1 and losing tied members.

Follow-ups to expect

• Return the top 3 members by friend count, ties included (DENSE_RANK on the counts).
• What if the same pair could be stored in both directions? (Normalise with MIN/MAX of the two ids and DISTINCT first.)
• Find friends-of-friends who are not yet friends (self join the stacked edge list).

In real work

Degree counts on undirected edge tables drive "most connected" badges, influencer detection and fraud-ring analysis in social and payments graphs.

Practice next