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 ts.employee_id, e.name, t.id as test_id, max(distinct ts.score) as total from test_results as ts join tests as t on ts.test_id = t.id join employees as e on ts.employee_id = e.id group by ts.employee_id, e.name, t.id) select employee_id, name as employee_name, sum(total) as total_score from cte group by employee_id, employee_name order by total_score desc, employee_id asc ;