Data Interview Qs - Reaching out to past candidates

Reaching out to past candidates

SQL Database Querying Window Function Subquery

Forward this email to a friend or colleague and challenge them to solve it.

Hi,

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

candidate_name	candidate_status
john_smith	Accepted
sarah_daniels	Declined
tim_cook	Accepted
lisa_perelli	Rejected
jenny_west	Rejected
karl_tran	Declined
tammy_turner	Declined

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).

candidate_name	interview_score
john_smith	4
sarah_daniels	5
tim_cook	4
lisa_perelli	2
jenny_west	2
karl_tran	4
tammy_turner	3

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.

You’ve accumulated 261 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.

Was this email forwarded to you? You can join our list here to receive questions