Avg interview scores by location
Question
Suppose your team is looking to see how interview scores may differ by location (while normalizing for differing roles) 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 by role/location, and then provide the team with next steps outlining how you might test to see if the interview scores do indeed differ by location (you can assume the actual tables will have enough n-count for common statistical tests).
Table: candidateInfo
name | role | location |
---|---|---|
john_smith | SWE | New York |
sarah_daniels | Analyst | San Francisco |
tim_cook | Analyst | San Francisco |
lisa_perelli | Data Scientist | New York |
jenny_west | Data Scientist | San Francisco |
karl_tran | SWE | New York |
tammy_turner | Analyst | San Francisco |
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 |
You can view/query the data in an interactive fiddle here.