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
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
Primary key: emp_id.
Table: EmployeeSkill
| Column | Type |
|---|---|
| emp_id | int |
| skill | varchar |
| level | enum(beginner, intermediate, expert) |
Primary key: emp_id, skill.
One row per skill an employee has declared and had assessed.
Examples
Example 1
Employee
| emp_id | full_name |
|---|---|
| 1 | Aarav Sharma |
| 2 | Diya Iyer |
| 3 | Ishaan Gupta |
| 4 | Kavya Nair |
EmployeeSkill
| emp_id | skill | level |
|---|---|---|
| 1 | Java | expert |
| 1 | SQL | expert |
| 1 | Docker | expert |
| 2 | Python | expert |
| 2 | Tableau | intermediate |
| 2 | Excel | expert |
| 3 | React | expert |
| 3 | Go | beginner |
| 4 | Kubernetes | intermediate |
Output
| emp_id | full_name | expert_count | expert_skills |
|---|---|---|---|
| 1 | Aarav Sharma | 3 | Docker, Java, SQL |
| 2 | Diya Iyer | 2 | Excel, 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_idAnother 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 →