Math Gymnastics SQL Query

Question

I have two tables of concern here: users and race_weeks. The user has many races, and race_week belongs to the User. Therefore, user_id is fk in the race_weeks table.

I need to do some complicated math in the fields in the race_weeks table in order to return users with the most accurate points.

Here are the fields we need to manipulate in the race_weeks table.

races_won (int)
races_lost (int)
races_tied (int)
points_won (int, pos or neg)
recordable_type(varchar, Robots can race, but we're only concerned about type 'User')

Just so that you fully understand the business logic here, during the week the user can participate in many races. The race_week entry represents the final results of this week's custom races. A user is considered active during the week if races_won, races_lost or races_tied is greater than 0. Otherwise, the user is inactive.

So, here is what we need to do in our request to return the users with the most points won (actually net_points_won):

• Calculate each user net_points_won (and not a field in the database).

• To calculate net_points_won, you take (1000 * count_of_active_weeks) - sum (points__won). (Why 1000? Imagine that each week a user discovers 1000 points in order to compete and participate in races. We want to refuse what we see from the user, because the user can enter only one race per week for 100 points, and sitting by 900, which we would skew, who actually earned the most points.)

This is a bit confusing, so let me know if I can clarify further.

Answer 1

, - : net_points , .

, races_won, races_lost races_tied , , .

SELECT user_id
     , SUM(points_won) - 1000 * COUNT(*) AS net_points
  FROM race_weeks
 WHERE recordable_type = 'User'
   AND (races_won > 0 OR races_lost > 0 OR races_tied > 0)
 GROUP BY user_id
 ORDER BY net_points DESC

Answer 2

SELECT  user_id, 1000 * COUNT(*) - SUM(points_won) AS net_points
FROM    race_weeks
WHERE   races_won + races_lost + races_tied
        AND recordable_type = 'User'
GROUP BY
        user_id
ORDER BY
        net_points DESC