MySQL: aggregate max value after calculation

August 15, 2023

I have an auto scorer for codes, and an overtime penalty strategy for my students: When he submits his code n minutes after a given deadline, his score will be multiplied by (100-min(n, 80))/100.

problem table:

id | deadline
 1 | 13:00

user_submission table:

id | uid | problem_id | time  | score
 1 | 101 |          1 | 10:00 | 80
 2 | 101 |          1 | 13:30 | 90
 3 | 102 |          1 | 13:30 | 90

Since the second submissions of user 101 and 102 exceeded the deadline by 30 minutes, their score needs to be multiplied by 0.7, which is 90*0.7=63.

Therefore, when querying for the highest score obtained by the user, the result should be as follows:

problem_id | uid | max_score
         1 | 101 | 80
         1 | 102 | 63
         2 | ...

How to construct this query? I have tried max() aggregation, but I could not use the problem.deadline field well.

>Solution :

We can try the following approach:

SELECT
    us.problem_id,
    us.uid,
    MAX(us.Score * CASE WHEN TIME_TO_SEC(SUBTIME(us.time, p.deadline)) > 0
                        THEN 1.0 - LEAST(80.0, TIME_TO_SEC(SUBTIME(us.time, p.deadline)) / 100.0)
                        ELSE 1.0 END) AS max_score
FROM user_submission us
INNER JOIN problem p
    ON p.id = us.problem_id
GROUP BY
    us.problem_id,
    us.uid;

The expression:

TIME_TO_SEC(SUBTIME(us.time, p.deadline))

will be a positive number whenever any given answer happen to be past the deadline. In that case, we multiply the score by your formula 100-min(n, 80) / 100. Note that we use the LEAST() scalar function here, not MIN(), as the latter is strictly an aggregate function.