Q#95: Reaching out to past candidates

Past Candidates

Note: I believe it should be free and readily available for everyone to gain value from data, hence I pledge to keep this series free regardless of how large it grows.

Suppose your team is looking to fill a new role, and wants to reach out to past candidates who had a successful interview but previously declined an offer at your company.

You’re given the below tables, detailing candidate offer statuses (‘Accepted’, ‘Declined’, ‘Rejected’) as well as associated interview scores. Using these tables, write a SQL query to return the top 2 candidates with the highest interview scores who ‘declined’ an offer.

Table: candidateStatus

Table: candidateInterviews

Each row represents a unique interview/candidate as well as the candidate’s score (an integer from 1–5, with 5 representing the highest possible score).

For simplicity, these tables are small, but you can assume in practicality the tables would be way too large to export or visually search through.

TRY IT YOURSELF

ANSWER

Data Scientists are used to solve a variety of problems, even HR ones. SQL is a tool in our toolkit to tackle these challenges and interviewers often test your SQL skills.

To accomplish this task, we’ll be working with two tables: candidateStatus and candidateInterviews.

The candidateStatus table contains information about the current status of candidates, including whether they 'Accepted,' 'Declined,' or 'Rejected' an offer. The candidateInterviews table stores the interview scores for each candidate. Each row represents a unique interview/candidate along with the interview score (an integer from 1-5, with 5 being the highest).

SQL Query

WITH DeclinedCandidates AS (
    SELECT
        cs.candidate_name,
        ci.interview_score
    FROM
        candidateStatus cs
    JOIN
        candidateInterviews ci
    ON
        cs.candidate_name = ci.candidate_name
    WHERE
        cs.candidate_status = 'Declined'
)

SELECT
    dc.candidate_name,
    dc.interview_score
FROM
    DeclinedCandidates dc
ORDER BY
    dc.interview_score DESC
LIMIT 2;

Query Explanation

Here's a step-by-step breakdown of the SQL query:

1. We start by creating a common table expression (CTE) called DeclinedCandidates to combine information from both tables (candidateStatus and candidateInterviews). We select the candidate names and interview scores for candidates who have a 'Declined' status in the candidateStatus table.
2. In the main part of the query, we select the candidate names and interview scores from the DeclinedCandidates CTE.
3. We use the ORDER BY clause to sort the results by interview scores in descending order, ensuring that candidates with the highest scores appear at the top.
4. Finally, we use the LIMIT clause to limit the results to the top 2 candidates with the highest interview scores among those who previously declined job offers.

Plug: Checkout all my digital products on Gumroad here. Please purchase ONLY if you have the means to do so. Use code: MEDSUB to get a 10% discount!

Tips and Donations