LinkedIn: Members With Exactly the Same Skill Set

Medium
740.

LinkedIn: Members With Exactly the Same Skill Set

The trust and safety team looks for clusters of profiles whose Skills section was copied wholesale, and the first signal is two members listing exactly the same set of skills.

Return every pair of members as member_a and member_b (with member_a < member_b) whose skill sets are identical: every skill one lists, the other lists too, and neither has a skill the other lacks. Sharing most skills is not enough — one extra or one missing skill breaks the match. Only members with at least one row in MemberSkill are considered. Order the rows by member_a, then member_b.

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: MemberSkill

ColumnType
member_idint
skillvarchar
added_ondate

Primary key (together): member_id, skill.

A skill listed on a member's profile, and when it was added. Skill names are always spelt the same way, so equal skills are equal strings.

Example

The rows must come back in the order shown.

MemberSkill

member_idskilladded_on
1SQL2024-01-10
1Python2024-02-11
2Python2024-03-05
2SQL2024-03-05
3SQL2024-04-01
3Python2024-04-01
3Excel2024-04-02
4SQL2024-05-20
5Figma2024-06-01
6Figma2024-06-15
1 more row

Output

member_amember_b
12
47
56
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: Members With Exactly the Same Skill Set

The trust and safety team looks for clusters of profiles whose Skills section was copied wholesale, and the first signal is two members listing exactly the…

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

Problem statement

The trust and safety team looks for clusters of profiles whose Skills section was copied wholesale, and the first signal is two members listing exactly the same set of skills.

Return every pair of members as member_a and member_b (with member_a < member_b) whose skill sets are identical: every skill one lists, the other lists too, and neither has a skill the other lacks. Sharing most skills is not enough — one extra or one missing skill breaks the match. Only members with at least one row in MemberSkill are considered. Order the rows by member_a, then member_b.

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: MemberSkill

ColumnType
member_idint
skillvarchar
added_ondate

Primary key: member_id, skill.

A skill listed on a member's profile, and when it was added. Skill names are always spelt the same way, so equal skills are equal strings.

Examples

Example 1

MemberSkill

member_idskilladded_on
1SQL2024-01-10
1Python2024-02-11
2Python2024-03-05
2SQL2024-03-05
3SQL2024-04-01
3Python2024-04-01
3Excel2024-04-02
4SQL2024-05-20
5Figma2024-06-01
6Figma2024-06-15
7SQL2024-07-07

Output

member_amember_b
12
47
56

How to solve LinkedIn: Members With Exactly the Same Skill Set

Set equality has no operator in SQL, so the query reduces it to counting. Two finite sets are equal exactly when they have the same size and their intersection is as large as either of them. The self join on skill (with b.member_id > a.member_id, so each pair is considered once and never with itself) produces one row per shared skill, so COUNT(*) per pair is the size of the intersection. The sizes CTE holds each member's own count; the pair qualifies when the two sizes are equal and the shared count reaches that size.

Both conditions are needed. Member 3 in the example shares SQL and Python with member 1 — the whole of member 1's set — but lists Excel as well, so their sizes differ and the pair fails. Pairs that share no skill never meet in the join, which is right because non-empty sets with nothing in common are not equal. The primary key rules out duplicate skills, so no count is inflated.

The first alternative builds a signature per member — the skills sorted and joined by GROUP_CONCAT — and matches equal signatures, which is compact and fast but relies on the separator never appearing inside a skill name. The second states equality literally as two-way containment with double NOT EXISTS, which needs no counting but probes the table for every pair.

Reference solution (MySQL)

WITH sizes AS (
  SELECT member_id, COUNT(*) AS n
  FROM MemberSkill
  GROUP BY member_id
)
SELECT a.member_id AS member_a, b.member_id AS member_b
FROM MemberSkill a
JOIN MemberSkill b ON b.skill = a.skill AND b.member_id > a.member_id
JOIN sizes sa ON sa.member_id = a.member_id
JOIN sizes sb ON sb.member_id = b.member_id
WHERE sa.n = sb.n
GROUP BY a.member_id, b.member_id, sa.n
HAVING COUNT(*) = sa.n
ORDER BY member_a, member_b

Another way

WITH sig AS (
  SELECT member_id, GROUP_CONCAT(skill ORDER BY skill SEPARATOR '|') AS skills
  FROM MemberSkill GROUP BY member_id
)
SELECT x.member_id AS member_a, y.member_id AS member_b
FROM sig x JOIN sig y ON y.skills = x.skills AND y.member_id > x.member_id
ORDER BY member_a, member_b

Another way

WITH m AS (SELECT DISTINCT member_id FROM MemberSkill)
SELECT x.member_id AS member_a, y.member_id AS member_b
FROM m x JOIN m y ON y.member_id > x.member_id
WHERE NOT EXISTS (SELECT 1 FROM MemberSkill s WHERE s.member_id = x.member_id
                  AND NOT EXISTS (SELECT 1 FROM MemberSkill t WHERE t.member_id = y.member_id AND t.skill = s.skill))
  AND NOT EXISTS (SELECT 1 FROM MemberSkill s WHERE s.member_id = y.member_id
                  AND NOT EXISTS (SELECT 1 FROM MemberSkill t WHERE t.member_id = x.member_id AND t.skill = s.skill))
ORDER BY member_a, member_b

← LinkedIn: Running Follower Count of Company Pages · LinkedIn: Mutual Connections Behind Pending Invitations →