LinkedIn: Skills Endorsed by Three or More Different People
The skills-graph team wants the skills that a broad set of members vouch for, not ones propped up by one enthusiastic endorser.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #726
Problem statement
The skills-graph team wants the skills that a broad set of members vouch for, not ones propped up by one enthusiastic endorser.
For each skill, count the different endorsers who endorsed it for someone other than themselves — a self-endorsement (endorser_id = member_id) does not count, and one person endorsing the same skill for several members counts once. Return skill and endorsers for the skills with at least 3 endorsers, ordered by endorsers descending, then skill 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: Endorsement
| Column | Type |
|---|---|
| endorsement_id | int |
| endorser_id | int |
| member_id | int |
| skill | varchar |
| endorsed_on | date |
Primary key: endorsement_id.
One row per endorsement: endorser_id vouched for member_id's skill. Skill names are always spelt the same way.
Examples
Example 1
Endorsement
| endorsement_id | endorser_id | member_id | skill | endorsed_on |
|---|---|---|---|---|
| 1 | 1 | 2 | SQL | 2025-02-01 |
| 2 | 3 | 2 | SQL | 2025-02-02 |
| 3 | 3 | 4 | SQL | 2025-02-03 |
| 4 | 4 | 4 | SQL | 2025-02-03 |
| 5 | 5 | 1 | SQL | 2025-02-05 |
| 6 | 1 | 5 | Python | 2025-02-06 |
| 7 | 2 | 5 | Python | 2025-02-07 |
| 8 | 4 | 3 | Python | 2025-02-08 |
| 9 | 2 | 1 | Figma | 2025-02-09 |
| 10 | 2 | 3 | Figma | 2025-02-10 |
Output
| skill | endorsers |
|---|---|
| Python | 3 |
| SQL | 3 |
How to solve LinkedIn: Skills Endorsed by Three or More Different People
Three rules shape the count. Self-endorsements are dropped first with WHERE endorser_id <> member_id, so they never reach a group. Then each skill forms a group, and inside it the number of different people is COUNT(DISTINCT endorser_id) — an endorser who backed SQL for two colleagues is still one person. Finally HAVING keeps the groups with at least three; a WHERE cannot see a count because it runs before grouping.
In the example, member 4 endorsing their own SQL is ignored and member 3's two SQL endorsements count once, so SQL has endorsers 1, 3 and 5 and makes the cut with exactly three. The alternative makes the de-duplication explicit: a derived table of distinct (skill, endorser) pairs, then a plain COUNT(*) per skill. Ties on the count are ordered by skill name so the result is fixed. The work is one scan plus a grouping of the remaining rows.
Reference solution (MySQL)
SELECT skill, COUNT(DISTINCT endorser_id) AS endorsers
FROM Endorsement
WHERE endorser_id <> member_id
GROUP BY skill
HAVING COUNT(DISTINCT endorser_id) >= 3
ORDER BY endorsers DESC, skillAnother way
SELECT skill, COUNT(*) AS endorsers FROM (SELECT DISTINCT skill, endorser_id FROM Endorsement WHERE endorser_id <> member_id) p GROUP BY skill HAVING COUNT(*) > 2 ORDER BY endorsers DESC, skill← LinkedIn: Active Premium Members per Plan on Quarter End · LinkedIn: Company Pages With No New Followers in May 2025 →