Student With the Most Study Buddies — SQL Medium Problem

Problem statement

The campus app lets two students link up as study buddies. Each link is stored once, with whichever student sent the request in student_a and the one who accepted in student_b — so a student's buddies are found on both sides of the table.

Return the student (or students) with the most buddies, with columns student_id and buddy_count. If several students share the highest count, return all of them. Return the rows in any order.

Tables

Table: BuddyPair

Primary key: student_a, student_b.

One row per accepted link. A pair of students appears at most once (never also reversed), and nobody is paired with themselves.

Examples

Example 1

BuddyPair

Output

How to solve Student With the Most Study Buddies

A link between two students is one row but two friendships-from-a-point-of-view: it adds a buddy to student_a and to student_b. So the first step turns each row into its two endpoints, by stacking the columns: SELECT student_a … UNION ALL SELECT student_b …. It must be UNION ALL — plain UNION removes duplicates, and a student who appears in three links must appear three times. Because the same pair is never stored twice, each appearance is a different buddy, and COUNT(*) grouped by student is the buddy count.

The statement asks for every student tied at the top, so ORDER BY … LIMIT 1 is wrong (it silently picks one). Instead, keep the counts in a CTE and filter with a scalar subquery: buddy_count = (SELECT MAX(buddy_count) FROM counts). RANK() OVER (ORDER BY COUNT(*) DESC) and keeping rank 1 does the same in one pass, since tied counts share rank 1.

A correlated alternative counts, for each distinct student, the links where they appear on either side (student_a = id OR student_b = id). It is easy to read but scans the table once per student; the union-and-group plan reads it twice and groups once.

Reference solution (MySQL)

WITH ends AS (
  SELECT student_a AS student_id FROM BuddyPair
  UNION ALL
  SELECT student_b FROM BuddyPair
), counts AS (
  SELECT student_id, COUNT(*) AS buddy_count
  FROM ends
  GROUP BY student_id
)
SELECT student_id, buddy_count
FROM counts
WHERE buddy_count = (SELECT MAX(buddy_count) FROM counts)

Another way

SELECT student_id, buddy_count FROM (
  SELECT student_id, COUNT(*) AS buddy_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM (SELECT student_a AS student_id FROM BuddyPair UNION ALL SELECT student_b FROM BuddyPair) e
  GROUP BY student_id
) ranked
WHERE rnk = 1

Another way

WITH ids AS (SELECT student_a AS student_id FROM BuddyPair UNION SELECT student_b FROM BuddyPair),
c AS (
  SELECT i.student_id,
         (SELECT COUNT(*) FROM BuddyPair p WHERE p.student_a = i.student_id OR p.student_b = i.student_id) AS buddy_count
  FROM ids i
)
SELECT student_id, buddy_count FROM c WHERE buddy_count = (SELECT MAX(buddy_count) FROM c)