Interview Guides

Rank Job Candidates by Score | Dataford Interview Questions - Dataford - Ace your Interview

Rank Job Candidates by Score

Easy

SQL & Data Manipulation

Window FunctionsRanking

Problem

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

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique identifier for each candidate
candidate_name	VARCHAR(100)	Candidate full name
technical_score	INT	Technical interview score
communication_score	INT	Communication interview score
application_status	VARCHAR(20)	Candidate application status

candidates

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
11	Ella Murphy	21	92	null
12	Ella Murphy	-12	88	offer_declined
13	Mia Gomez	59	84	disqualified
14	Noah Kim	83	90	active
15	Ryan Cooper	82	87	withdrawn
16	Caleb Foster	-11	76	null
17	Ryan Cooper	53	93	assessment_pending
18	Julian Price	40	72	pending
19	Mia Gomez	99	91	closed
20	Ethan Brooks	89	66	manager_round
21	Chloe Martin	65	69	interviewing
22	Liam Chen	-9	73	final_round
23	Olivia Turner	-6	66	assessment_complete
24	Grace Lee	34	86	reference_check
25	Julian Price	73	null	on_hold
26	Leo Ward	80	90	assessment_pending
27	Ryan Cooper	78	null	technical_round
28	Leo Ward	-16	86	phone_screen
29	Grace Lee	-12	96	offer_sent

Expected Output

candidate_name	total_score
Priya Shah	183
Ava Patel	178
Ethan Brooks	178
Emma Davis	173
Noah Kim	173
Liam Chen	160
Mia Gomez	140
Chloe Martin	95
Lucas Reed	null

Problem

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

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique identifier for each candidate
candidate_name	VARCHAR(100)	Candidate full name
technical_score	INT	Technical interview score
communication_score	INT	Communication interview score
application_status	VARCHAR(20)	Candidate application status

candidates

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
11	Ella Murphy	21	92	null
12	Ella Murphy	-12	88	offer_declined
13	Mia Gomez	59	84	disqualified
14	Noah Kim	83	90	active
15	Ryan Cooper	82	87	withdrawn
16	Caleb Foster	-11	76	null
17	Ryan Cooper	53	93	assessment_pending
18	Julian Price	40	72	pending
19	Mia Gomez	99	91	closed
20	Ethan Brooks	89	66	manager_round
21	Chloe Martin	65	69	interviewing
22	Liam Chen	-9	73	final_round
23	Olivia Turner	-6	66	assessment_complete
24	Grace Lee	34	86	reference_check
25	Julian Price	73	null	on_hold
26	Leo Ward	80	90	assessment_pending
27	Ryan Cooper	78	null	technical_round
28	Leo Ward	-16	86	phone_screen
29	Grace Lee	-12	96	offer_sent

Expected Output

candidate_name	total_score
Priya Shah	183
Ava Patel	178
Ethan Brooks	178
Emma Davis	173
Noah Kim	173
Liam Chen	160
Mia Gomez	140
Chloe Martin	95
Lucas Reed	null

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Rank Top Clients by Product RevenueMedium

Monthly Campaign Performance SummaryEasy

Summarize Survey Ratings by QuestionEasy

Next question

Rank Job Candidates by Score

Easy

SQL & Data Manipulation

Window FunctionsRanking

Problem

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

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique identifier for each candidate
candidate_name	VARCHAR(100)	Candidate full name
technical_score	INT	Technical interview score
communication_score	INT	Communication interview score
application_status	VARCHAR(20)	Candidate application status

candidates

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
11	Ella Murphy	21	92	null
12	Ella Murphy	-12	88	offer_declined
13	Mia Gomez	59	84	disqualified
14	Noah Kim	83	90	active
15	Ryan Cooper	82	87	withdrawn
16	Caleb Foster	-11	76	null
17	Ryan Cooper	53	93	assessment_pending
18	Julian Price	40	72	pending
19	Mia Gomez	99	91	closed
20	Ethan Brooks	89	66	manager_round
21	Chloe Martin	65	69	interviewing
22	Liam Chen	-9	73	final_round
23	Olivia Turner	-6	66	assessment_complete
24	Grace Lee	34	86	reference_check
25	Julian Price	73	null	on_hold
26	Leo Ward	80	90	assessment_pending
27	Ryan Cooper	78	null	technical_round
28	Leo Ward	-16	86	phone_screen
29	Grace Lee	-12	96	offer_sent

Expected Output

candidate_name	total_score
Priya Shah	183
Ava Patel	178
Ethan Brooks	178
Emma Davis	173
Noah Kim	173
Liam Chen	160
Mia Gomez	140
Chloe Martin	95
Lucas Reed	null

Problem

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

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique identifier for each candidate
candidate_name	VARCHAR(100)	Candidate full name
technical_score	INT	Technical interview score
communication_score	INT	Communication interview score
application_status	VARCHAR(20)	Candidate application status

candidates

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
11	Ella Murphy	21	92	null
12	Ella Murphy	-12	88	offer_declined
13	Mia Gomez	59	84	disqualified
14	Noah Kim	83	90	active
15	Ryan Cooper	82	87	withdrawn
16	Caleb Foster	-11	76	null
17	Ryan Cooper	53	93	assessment_pending
18	Julian Price	40	72	pending
19	Mia Gomez	99	91	closed
20	Ethan Brooks	89	66	manager_round
21	Chloe Martin	65	69	interviewing
22	Liam Chen	-9	73	final_round
23	Olivia Turner	-6	66	assessment_complete
24	Grace Lee	34	86	reference_check
25	Julian Price	73	null	on_hold
26	Leo Ward	80	90	assessment_pending
27	Ryan Cooper	78	null	technical_round
28	Leo Ward	-16	86	phone_screen
29	Grace Lee	-12	96	offer_sent

Expected Output

candidate_name	total_score
Priya Shah	183
Ava Patel	178
Ethan Brooks	178
Emma Davis	173
Noah Kim	173
Liam Chen	160
Mia Gomez	140
Chloe Martin	95
Lucas Reed	null

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Rank Top Clients by Product RevenueMedium

Monthly Campaign Performance SummaryEasy

Summarize Survey Ratings by QuestionEasy

Next question