LinkedIn: Skills Endorsed by Three or More Different People

Easy
726.

LinkedIn: Skills Endorsed by Three or More Different People

EasyAggregation

MySQL

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

ColumnType
endorsement_idint
endorser_idint
member_idint
skillvarchar
endorsed_ondate

Primary key: endorsement_id.

One row per endorsement: endorser_id vouched for member_id's skill. Skill names are always spelt the same way.

Example

The rows must come back in the order shown.

Endorsement

endorsement_idendorser_idmember_idskillendorsed_on
112SQL2025-02-01
232SQL2025-02-02
334SQL2025-02-03
444SQL2025-02-03
551SQL2025-02-05
615Python2025-02-06
725Python2025-02-07
843Python2025-02-08
921Figma2025-02-09
1023Figma2025-02-10

Output

skillendorsers
Python3
SQL3
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: 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

ColumnType
endorsement_idint
endorser_idint
member_idint
skillvarchar
endorsed_ondate

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_idendorser_idmember_idskillendorsed_on
112SQL2025-02-01
232SQL2025-02-02
334SQL2025-02-03
444SQL2025-02-03
551SQL2025-02-05
615Python2025-02-06
725Python2025-02-07
843Python2025-02-08
921Figma2025-02-09
1023Figma2025-02-10

Output

skillendorsers
Python3
SQL3

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, skill

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