Assessing candidate status in SQL
Question
You're given the following table showing information on candidates interviewing at your company. You'll notice there can be multiple records per candidate, as the table logs each step in the hiring process (at the interview stage and beyond) as outlined below:
'interview' - occurs 1-N times for each candidate logged
'rejected' - the candidate was rejected from the position after interviewing
'offer' - the candidate was offered a position after interviewing
Given this information, write a SQL query to return the number of unique candidates who have been rejected, offered a position, or (if they have not been rejected / offered a role yet) are actively interviewing.
Table: candidate_log
| candidate_id | status |
|---|---|
| 1 | interview |
| 1 | rejected |
| 2 | interview |
| 3 | interview |
| 3 | offer |
| 4 | interview |
| 4 | interview |
| 4 | rejected |
You can view/query the data in an interactive SQL fiddle here.