LinkedIn: Connection Count of Every Member — SQL Problem
The network-health dashboard shows each member's number of first-degree connections.
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #731
Problem statement
The network-health dashboard shows each member's number of first-degree connections. A connection is stored once per pair, with the smaller member id in member_a.
Return member_id, full_name and connections for every member, where connections counts the pairs the member appears in on either side; members with no connections show 0. Order the rows by connections descending, then member_id ascending.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: Member
| Column | Type |
|---|---|
| member_id | int |
| full_name | varchar |
Primary key: member_id.
One row per member.
Table: MemberConnection
| Column | Type |
|---|---|
| member_a | int |
| member_b | int |
| connected_on | date |
Primary key: member_a, member_b.
One row per connected pair, always with member_a < member_b; both are member ids.
Examples
Example 1
Member
| member_id | full_name |
|---|---|
| 1 | Ananya Menon |
| 2 | Farhan Khan |
| 3 | Ira Bose |
| 4 | Karan Mehta |
| 5 | Pooja Reddy |
MemberConnection
| member_a | member_b | connected_on |
|---|---|---|
| 1 | 2 | 2024-08-01 |
| 1 | 3 | 2024-09-12 |
| 2 | 3 | 2024-10-05 |
| 3 | 4 | 2025-01-20 |
Output
| member_id | full_name | connections |
|---|---|---|
| 3 | Ira Bose | 3 |
| 1 | Ananya Menon | 2 |
| 2 | Farhan Khan | 2 |
| 4 | Karan Mehta | 1 |
| 5 | Pooja Reddy | 0 |
How to solve LinkedIn: Connection Count of Every Member
Storing each connection once keeps the table half the size, but it means a member's connections are split across two columns: the pairs where they are member_a and the pairs where they are member_b. The CTE stacks both columns into one list of endpoints with UNION ALL — every pair contributes one row for each of its members. UNION would be wrong here: it removes duplicate values, so a member with three connections would appear once.
From there it is a LEFT JOIN from Member and a count of the matched endpoints, so members who appear nowhere still get a row with 0. The first alternative counts the pairs per member with a correlated subquery and an OR; the second joins on m.member_id IN (c.member_a, c.member_b), which matches each pair once per member it involves. The UNION ALL version is usually fastest with an index on each column, since the OR conditions are harder to index. Ties on the count are broken by member id.
Reference solution (MySQL)
WITH ends AS (
SELECT member_a AS member_id FROM MemberConnection
UNION ALL
SELECT member_b FROM MemberConnection
)
SELECT m.member_id, m.full_name, COUNT(e.member_id) AS connections
FROM Member m
LEFT JOIN ends e ON e.member_id = m.member_id
GROUP BY m.member_id, m.full_name
ORDER BY connections DESC, m.member_idAnother way
SELECT m.member_id, m.full_name, (SELECT COUNT(*) FROM MemberConnection c WHERE c.member_a = m.member_id OR c.member_b = m.member_id) AS connections FROM Member m ORDER BY connections DESC, m.member_idAnother way
SELECT m.member_id, m.full_name, COUNT(c.member_a) AS connections FROM Member m LEFT JOIN MemberConnection c ON m.member_id IN (c.member_a, c.member_b) GROUP BY m.member_id, m.full_name ORDER BY connections DESC, m.member_id← LinkedIn: Easy Apply Share of Applications per Job · LinkedIn: Invitation Acceptance With and Without a Note →