Average interview scores by interview outcome
Question
Suppose your team is looking to see how interview scores may differ by interview outcome (if an offer was extended) and by role at your company.
You're given the below tables, detailing candidate roles/locations as well as associated interview scores. Using these tables, write a SQL query to return the average interview scores for each interview outcome (e.g. was a job offer extended) by role and interview_id. The resulting query should have the following fields:
- role
- interview_id
- avg_score_no_offer
- avg_score_offer
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). Each candidate should have 3 interviews.
candidate_name | interview_id | interview_score |
---|---|---|
john_smith | 1 | 5 |
sarah_daniels | 1 | 4 |
tim_cook | 1 | 2 |
... | ... | ... |
Table: candidateInterviews
Each row represents a unique candidate and the result of their interview (e.g. did they get an offer?). "has_offer" is a boolean field, 1 = candidate had an offer extended and 0 = no offer extended.
name | has_offer | role | location |
---|---|---|---|
john_smith | 1 | SWE | New York |
sarah_daniels | 1 | Analyst | San Francisco |
tim_cook | 0 | Analyst | San Francisco |
... | ... | ... | ... |
You can view/query the data in an interactive fiddle here.