Average interview scores by interview outcome

Average interview scores by interview outcome

SQL Database Querying Subquery Case Statements Join

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

Hi,

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.

You’ve accumulated 397 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