LinkedIn: Skill Overlap
Linked In, Glassdoor
Problem Statement
LinkedIn’s recommendation engine needs to highlight "Top Applicants" for recruiters. A top applicant is defined as someone who possesses at least 80% of the skills listed as required for a job.
Write a query to find all such users for Job ID 1001.
Rules:
- Only consider skills that are explicitly listed as required for Job ID 1001 in the job_requirements table.
- A user must have the exact skill name (case-sensitive) in the user_skills table to count as a match.
- The match percentage should be calculated as: (number of matching skills / total number of skills required for the job).
- Only return users with a match percentage >= 80%.
- Output columns: user_id, matched_skills_count, match_percentage.
- Result must be sorted by match_percentage descending, then user_id ascending.
Tests your understanding of
Joins, Aggregation, Filtering and Division
Input Tables
job_requirements
| job_id(INTEGER) | skill_name(VARCHAR) |
|---|---|
| 1001 | SQL |
| 1001 | Python |
| 1001 | Tableau |
| 1001 | AWS |
| 1001 | Spark |
| 1002 | Java |
| 1002 | Docker |
user_skills
| user_id(INTEGER) | skill_name(VARCHAR) |
|---|---|
| 1 | SQL |
| 1 | Python |
| 1 | Tableau |
| 1 | AWS |
| 1 | Spark |
| 2 | SQL |
| 2 | Python |
| 2 | Tableau |
| 2 | AWS |
| 3 | SQL |
| 3 | Python |
| 3 | C++ |
| 4 | Java |
| 5 | SQL |
| 5 | Python |
| 5 | Tableau |
| 5 | Spark |
Expected Output
| user_id(INTEGER) | matched_skills_count(INTEGER) | match_percentage(DECIMAL) |
|---|---|---|
| 1 | 5 | 100 |
| 2 | 4 | 80 |
| 5 | 4 | 80 |
Tags
HardJoinsAggregationFilteringDivision
20-30 min
42%