Reaching out to past candidates
Question
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.