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.