LinkedIn: Connection Count of Every Member

Medium
731.

LinkedIn: Connection Count of Every Member

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

ColumnType
member_idint
full_namevarchar

Primary key: member_id.

One row per member.

Table: MemberConnection

ColumnType
member_aint
member_bint
connected_ondate

Primary key (together): member_a, member_b.

One row per connected pair, always with member_a < member_b; both are member ids.

Example

The rows must come back in the order shown.

Member

member_idfull_name
1Ananya Menon
2Farhan Khan
3Ira Bose
4Karan Mehta
5Pooja Reddy

MemberConnection

member_amember_bconnected_on
122024-08-01
132024-09-12
232024-10-05
342025-01-20

Output

member_idfull_nameconnections
3Ira Bose3
1Ananya Menon2
2Farhan Khan2
4Karan Mehta1
5Pooja Reddy0
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.

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

ColumnType
member_idint
full_namevarchar

Primary key: member_id.

One row per member.

Table: MemberConnection

ColumnType
member_aint
member_bint
connected_ondate

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_idfull_name
1Ananya Menon
2Farhan Khan
3Ira Bose
4Karan Mehta
5Pooja Reddy

MemberConnection

member_amember_bconnected_on
122024-08-01
132024-09-12
232024-10-05
342025-01-20

Output

member_idfull_nameconnections
3Ira Bose3
1Ananya Menon2
2Farhan Khan2
4Karan Mehta1
5Pooja Reddy0

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_id

Another 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_id

Another 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 →