Assessing candidate status in SQL


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.


Access restricted

Subscribe to premium account to see the solution.

Get premium now