Task
BrightHire wants a simple leaderboard of candidates based on their interview evaluation data. Write a SQL query to rank candidates by their total score using a single table.
Requirements
- Return each
candidate_name and their total_score as the sum of technical_score and communication_score.
- Include only candidates where
application_status = 'active'.
- Order the results by
total_score descending, then candidate_name ascending.
Table Definition
| column_name | type | description |
|---|
| candidate_id | INT | Unique candidate identifier |
| candidate_name | VARCHAR(100) | Candidate full name |
| technical_score | INT | Technical interview score |
| communication_score | INT | Communication interview score |
| application_status | VARCHAR(20) | Current application status |
Sample Data
| candidate_id | candidate_name | technical_score | communication_score | application_status |
|---|
| 4 | Priya Shah | 92 | 91 | active |
| 1 | Ava Patel | 88 | 90 | active |
| 7 | Noah Kim | 95 | 80 | withdrawn |
| 2 | Liam Chen | 76 | 84 | active |
| 9 | Mia Gomez | 70 | 70 | active |
| 5 | Ethan Brooks | 88 | 90 | active |
| 3 | Sofia Rivera | 90 | 85 | rejected |
| 8 | Lucas Reed | 60 | NULL | active |
| 6 | Chloe Martin | 0 | 95 | active |
| 10 | Emma Davis | 85 | 88 | active |
Expected Output
| candidate_name | total_score |
|---|
| Priya Shah | 183 |
| Ava Patel | 178 |
| Ethan Brooks | 178 |
| Emma Davis | 173 |
| Liam Chen | 160 |
| Mia Gomez | 140 |
| Chloe Martin | 95 |
| Lucas Reed | NULL |