Job acceptance rates by location in SQL
Question
Suppose you're trying to determine which locations drive a higher offer acceptance rate at your company.
You're given the below tables, detailing candidate offer statuses ('Accepted', 'Declined', 'Rejected') as well as a table containing the location of the role each candidate is interviewing for. Using these tables, write a SQL query to return the percent of offers given that are accepted by location.
Note: If a candidate's status is in 'Accepted' or 'Declined' that means that they have received an offer, while if the status is in 'Rejected' that candidate did not receive an offer.
Table: candidateStatus
candidate_id | status |
---|---|
13063 | Accepted |
10281 | Declined |
10648 | Accepted |
12661 | Rejected |
12180 | Rejected |
12855 | Declined |
12886 | Declined |
11850 | Accepted |
11535 | Declined |
13651 | Rejected |
14700 | Declined |
13582 | Accepted |
12179 | Accepted |
14345 | Accepted |
Table: candidateInterviewLocation
candidate_id | role_location |
---|---|
13063 | San Francisco |
10281 | New York |
10648 | New York |
12661 | New York |
12180 | San Francisco |
12855 | New York |
12886 | New York |
11850 | San Francisco |
11535 | New York |
13651 | San Francisco |
14700 | New York |
13582 | San Francisco |
12179 | San Francisco |
14345 | San Francisco |
Click here to view/query these tables in an interactive SQL fiddle.