Staffing Pool of Multi-Skill Experts

Medium
240.

Staffing Pool of Multi-Skill Experts

The resource-management team is staffing a new client project and wants people who are expert in at least two skills. Skills at beginner or intermediate level do not count towards the two.

Return emp_id, full_name, expert_count and expert_skills — the names of their expert skills in alphabetical order, separated by a comma and a space (Docker, Java, SQL). Order by expert_count descending, then emp_id ascending.

Tables

Table: Employee

ColumnType
emp_idint
full_namevarchar

Primary key: emp_id.

Table: EmployeeSkill

ColumnTypeValues
emp_idint
skillvarchar
levelenumbeginner, intermediate, expert

Primary key (together): emp_id, skill.

One row per skill an employee has declared and had assessed.

Example

The rows must come back in the order shown.

Employee

emp_idfull_name
1Aarav Sharma
2Diya Iyer
3Ishaan Gupta
4Kavya Nair

EmployeeSkill

emp_idskilllevel
1Javaexpert
1SQLexpert
1Dockerexpert
2Pythonexpert
2Tableauintermediate
2Excelexpert
3Reactexpert
3Gobeginner
4Kubernetesintermediate

Output

emp_idfull_nameexpert_countexpert_skills
1Aarav Sharma3Docker, Java, SQL
2Diya Iyer2Excel, Python
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.

Staffing Pool of Multi-Skill Experts — SQL Strings Problem

The resource-management team is staffing a new client project and wants people who are expert in at least two skills.

  • Difficulty: Medium
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #240

Problem statement

The resource-management team is staffing a new client project and wants people who are expert in at least two skills. Skills at beginner or intermediate level do not count towards the two.

Return emp_id, full_name, expert_count and expert_skills — the names of their expert skills in alphabetical order, separated by a comma and a space (Docker, Java, SQL). Order by expert_count descending, then emp_id ascending.

Tables

Table: Employee

ColumnType
emp_idint
full_namevarchar

Primary key: emp_id.

Table: EmployeeSkill

ColumnType
emp_idint
skillvarchar
levelenum(beginner, intermediate, expert)

Primary key: emp_id, skill.

One row per skill an employee has declared and had assessed.

Examples

Example 1

Employee

emp_idfull_name
1Aarav Sharma
2Diya Iyer
3Ishaan Gupta
4Kavya Nair

EmployeeSkill

emp_idskilllevel
1Javaexpert
1SQLexpert
1Dockerexpert
2Pythonexpert
2Tableauintermediate
2Excelexpert
3Reactexpert
3Gobeginner
4Kubernetesintermediate

Output

emp_idfull_nameexpert_countexpert_skills
1Aarav Sharma3Docker, Java, SQL
2Diya Iyer2Excel, Python

How to solve Staffing Pool of Multi-Skill Experts

Filter, group, and aggregate into a string. WHERE s.level = 'expert' removes beginner and intermediate skills before grouping, so they affect neither the count nor the list. Grouping by employee, COUNT(*) is the number of expert skills and HAVING COUNT(*) >= 2 keeps the multi-skill experts.

GROUP_CONCAT(s.skill ORDER BY s.skill SEPARATOR ', ') builds the list. The inner ORDER BY is essential: without it the order inside the string follows however the rows were read, and the same data could produce a different string. The separator is a comma followed by a space, as the statement shows.

The alternative aggregates the skills table alone in a derived table and joins the names afterwards, which keeps the join small when the skills table is large. Either way it is one pass over the expert rows plus a sort within each employee's handful of skills; the final order is the biggest pool first, ties by emp_id.

Reference solution (MySQL)

SELECT e.emp_id, e.full_name, COUNT(*) AS expert_count,
       GROUP_CONCAT(s.skill ORDER BY s.skill SEPARATOR ', ') AS expert_skills
FROM Employee e
JOIN EmployeeSkill s ON s.emp_id = e.emp_id
WHERE s.level = 'expert'
GROUP BY e.emp_id, e.full_name
HAVING COUNT(*) >= 2
ORDER BY expert_count DESC, e.emp_id

Another way

SELECT e.emp_id, e.full_name, x.expert_count, x.expert_skills
FROM Employee e
JOIN (SELECT emp_id, COUNT(skill) AS expert_count, GROUP_CONCAT(skill ORDER BY skill SEPARATOR ', ') AS expert_skills
      FROM EmployeeSkill WHERE level = 'expert' GROUP BY emp_id) x ON x.emp_id = e.emp_id
WHERE x.expert_count > 1
ORDER BY x.expert_count DESC, e.emp_id

← Monthly Attrition Rate in 2024 · Org Chart Depth and Business Head of Every Employee →