Calculate Test Scores
An organization has conducted SQL tests for all its backend developers. A developer could attempt a test multiple times. The organization wants to calculate the overall scores of the candidate for the SQL tests.
Write a query to print the employee_id, employee_name, and the total_score obtained by the developers. A developer's total_score is the sum of their maximum scores for all tests. If the developer has attempted a single test multiple times, consider the maximum score of all the attempts for that test.
Order the results in descending total_score. In case more than one developer has obtained a similar total_score, sort the results by ascending order of employee_id. The schema of the tables is described below.
tests +------+---------+ | id | int | <----------+ | name | varchar | | +------+---------+ | | employees | +------+---------+ | | id | int | <-------+ | | name | varchar | | | +------+---------+ | | | | test_results | | +-------------+---------+ | | | id | int | | | | employee_id | int | -+ | | test_id | int | ----+ | score | decimal | +-------------+---------+
Here's our solution to the problem:
SELECT e.id as employee_id,
e.name as employee_name,
SUM(max_score) AS total_score
FROM employees e
INNER JOIN (
SELECT employee_id, MAX(score) as max_score
FROM test_results
GROUP BY employee_id, test_id
) r
ON r.employee_id = e.id
GROUP BY e.id, e.name
ORDER BY total_score DESC, e.id ASC;
With cte as ( SELECT t.employee_id, e.name as employee_name, t.test_id, score, DENSE_RANK() OVER(Partition by employee_id, test_id ORDER BY score DESC) as score_rank FROM employees as e JOIN test_results as t On e.id = t.employee_id order by t.employee_id ASC ) SELECT employee_id, employee_name, SUM(score) as total_score FROM cte WHERE score_rank = 1 GROUP BY employee_id, employee_name ORDER BY total_score DESC, employee_id ASC