SQL Puzzle Series 3: LinkedIn’s “top applicant” feature
While scrolling through LinkedIn you will notice this feature that lets you know if you are a top applicant or not. How is this judged? Well, from what information is available online it's based on the information available on your profile most notably the skills section.
In this case for the job posting this candidate is considered “a top applicant” amongst the current pool of candidates and has 5 out of the 10 skills needed.
For the applicant to be considered “competitive”, they would potentially need to have ≥ 5 of the 10 skills needed.
Let’s write a SQL query to see if we can weed out the candidate pool from all the candidates we have. For simplicity we start with 2 candidates A and B. Below is the list of skills the candidates individually possess.
Now between A and B the candidate which would see the “Top Candidate” flag would be the one who meets at least 5 out of the 10 needed requirements for the job.
The job description has the following 10 requirements:
The query that is needed should be able to give out just the name of the candidate that meets the criteria i.e., having more than 5 out of the 10 job requirements.
Simple enough logic.
The data of the candidates are stored in table labelled #Candidates and the table with the required skills are labelled #Requirements.
The query that is needed would look something like this, where we are using a subquery with the required skills as a filter and then using the GROUP BY and HAVING clauses to weed out the candidates who have less than 5 of the required skills.
Let's validated to see if this is actually what happens.
As you can see even manually checking yields the same results.
So, now to scale this query and check amongst a candidate pool of 100s of applicants the data would simply be stored in the #Candidates table and the same process would repeat.
The code can be found here.
References/Resources:
