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 |
+-------------+---------+