LinkedIn: Top Endorsed Skill per Industry and Its Lead

Hard
746.

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

ColumnType
member_idint
industryvarchar

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

ColumnType
endorsement_idint
endorser_idint
member_idint
skillvarchar

Primary key: endorsement_id.

One row per endorsement received by member_id; member_id always names a member.

Example

The rows must come back in the order shown.

Member

member_idindustry
1Software
2Software
3Banking
4Banking
5Healthcare
6NULL

Endorsement

endorsement_idendorser_idmember_idskill
191Python
281Python
372Python
492Java
534Excel
653SQL
713Excel
824SQL
915Leadership
1026Python
1 more row

Output

industrytop_skillendorsementslead_over_next
BankingExcel20
HealthcareLeadership11
SoftwarePython32
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: 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

ColumnType
member_idint
industryvarchar

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

ColumnType
endorsement_idint
endorser_idint
member_idint
skillvarchar

Primary key: endorsement_id.

One row per endorsement received by member_id; member_id always names a member.

Examples

Example 1

Member

member_idindustry
1Software
2Software
3Banking
4Banking
5Healthcare
6NULL

Endorsement

endorsement_idendorser_idmember_idskill
191Python
281Python
372Python
492Java
534Excel
653SQL
713Excel
824SQL
915Leadership
1026Python
1136Python

Output

industrytop_skillendorsementslead_over_next
BankingExcel20
HealthcareLeadership11
SoftwarePython32

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.industry

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

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