Student With the Most Study Buddies

Medium
28.

Student With the Most Study Buddies

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

ColumnType
student_aint
student_bint
paired_ondate

Primary key (together): 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

BuddyPair

student_astudent_bpaired_on
122025-07-01
132025-07-02
232025-07-05
422025-07-09
512025-07-12

Output

student_idbuddy_count
13
23
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Student With the Most Study Buddies — SQL Subqueries Problem

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…

  • Difficulty: Medium
  • Topics: Subqueries, Aggregation
  • Dialect: MySQL
  • Problem: #28

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

ColumnType
student_aint
student_bint
paired_ondate

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

student_astudent_bpaired_on
122025-07-01
132025-07-02
232025-07-05
422025-07-09
512025-07-12

Output

student_idbuddy_count
13
23

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)

← Swap Neighbouring Seats in the Exam Hall · Canteen Menu Prices on 15 March →