LinkedIn: Top Endorsed Skill per Industry and Its Lead
For the industry reports in LinkedIn's talent insights, the economic-graph team names the skill that members in each industry are endorsed for most, and how…
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #746
Problem statement
For the industry reports in LinkedIn's talent insights, the economic-graph team names the skill that members in each industry are endorsed for most, and how clear its lead is.
Count endorsements per skill among those received by members of each industry (members with a NULL industry are ignored). For each industry with at least one such endorsement, return industry, top_skill, endorsements (its count) and lead_over_next: its count minus the count of the next skill in that industry. Rank skills by count descending, ties broken by skill name alphabetically, so tied skills give a lead of 0. If the industry has only one endorsed skill, lead_over_next equals its count. Order the rows by industry.
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 |
| industry | varchar |
Primary key: member_id.
One row per member; industry is the member's current industry, or NULL if they have not set one.
Table: Endorsement
| Column | Type |
|---|---|
| endorsement_id | int |
| endorser_id | int |
| member_id | int |
| skill | varchar |
Primary key: endorsement_id.
One row per endorsement received by member_id; member_id always names a member.
Examples
Example 1
Member
| member_id | industry |
|---|---|
| 1 | Software |
| 2 | Software |
| 3 | Banking |
| 4 | Banking |
| 5 | Healthcare |
| 6 | NULL |
Endorsement
| endorsement_id | endorser_id | member_id | skill |
|---|---|---|---|
| 1 | 9 | 1 | Python |
| 2 | 8 | 1 | Python |
| 3 | 7 | 2 | Python |
| 4 | 9 | 2 | Java |
| 5 | 3 | 4 | Excel |
| 6 | 5 | 3 | SQL |
| 7 | 1 | 3 | Excel |
| 8 | 2 | 4 | SQL |
| 9 | 1 | 5 | Leadership |
| 10 | 2 | 6 | Python |
| 11 | 3 | 6 | Python |
Output
| industry | top_skill | endorsements | lead_over_next |
|---|---|---|---|
| Banking | Excel | 2 | 0 |
| Healthcare | Leadership | 1 | 1 |
| Software | Python | 3 | 2 |
How to solve LinkedIn: Top Endorsed Skill per Industry and Its Lead
First the endorsements are attributed to industries: join each endorsement to the member who received it and count per (industry, skill), discarding members without an industry. That turns the question into ranking a short list per industry.
ROW_NUMBER() with ORDER BY cnt DESC, skill gives a strict order — the skill name breaks ties so the top is always one skill, and the next skill after a tie has the same count, giving a lead of 0. The answer reads two rows of that order: rank 1 is the top skill, rank 2 the runner-up. A LEFT JOIN to rank 2 keeps industries with a single skill, where COALESCE(s.cnt, 0) makes the lead the full count.
The first alternative reads the runner-up's count from the same row with LEAD(cnt, 1, 0), whose third argument supplies 0 when there is no next row. The second finds the top skill with NOT EXISTS and the runner-up's count as the largest count among the other skills — the same number as rank 2's, since a tied skill is the largest other one. All three group the endorsements once; the per-industry lists are small.
Reference solution (MySQL)
WITH counts AS (
SELECT m.industry, e.skill, COUNT(*) AS cnt
FROM Endorsement e
JOIN Member m ON m.member_id = e.member_id
WHERE m.industry IS NOT NULL
GROUP BY m.industry, e.skill
), ranked AS (
SELECT industry, skill, cnt,
ROW_NUMBER() OVER (PARTITION BY industry ORDER BY cnt DESC, skill) AS rn
FROM counts
)
SELECT t.industry, t.skill AS top_skill, t.cnt AS endorsements,
t.cnt - COALESCE(s.cnt, 0) AS lead_over_next
FROM ranked t
LEFT JOIN ranked s ON s.industry = t.industry AND s.rn = 2
WHERE t.rn = 1
ORDER BY t.industryAnother way
WITH counts AS (
SELECT m.industry, e.skill, COUNT(*) AS cnt FROM Endorsement e JOIN Member m ON m.member_id = e.member_id
WHERE m.industry IS NOT NULL GROUP BY m.industry, e.skill
), ordered AS (
SELECT industry, skill, cnt,
ROW_NUMBER() OVER (PARTITION BY industry ORDER BY cnt DESC, skill) AS rn,
LEAD(cnt, 1, 0) OVER (PARTITION BY industry ORDER BY cnt DESC, skill) AS next_cnt
FROM counts
)
SELECT industry, skill AS top_skill, cnt AS endorsements, cnt - next_cnt AS lead_over_next
FROM ordered WHERE rn = 1 ORDER BY industryAnother way
WITH counts AS (
SELECT m.industry, e.skill, COUNT(*) AS cnt FROM Endorsement e JOIN Member m ON m.member_id = e.member_id
WHERE m.industry IS NOT NULL GROUP BY m.industry, e.skill
), top1 AS (
SELECT c.* FROM counts c WHERE NOT EXISTS (
SELECT 1 FROM counts o WHERE o.industry = c.industry AND (o.cnt > c.cnt OR (o.cnt = c.cnt AND o.skill < c.skill)))
)
SELECT t.industry, t.skill AS top_skill, t.cnt AS endorsements,
t.cnt - COALESCE((SELECT MAX(o.cnt) FROM counts o WHERE o.industry = t.industry AND o.skill <> t.skill), 0) AS lead_over_next
FROM top1 t ORDER BY t.industry← LinkedIn: First- and Second-Degree Network Size · LinkedIn: Boomerang Hires in Members' Work History →