OtaMota
Legend
I have a rather long request.
I want a query that for a competitior, since the day of their first competition, each day is checked with the following formula.
Note: There will be two notations. PWR100 is WR100 of the day checked, CWR100 is today’s WR100 (as of writing July 2025). P ranking is similar, world rank as of day checked
1. Check if competitor is PWR100 or better
2. If yes, calculate their points, done as follows
2a. 100/(competitor’s P ranking) * (PWR100 time / competitor’s P ranking time) * (CWR100 / PWR100) = Day points (round this number to nearest whole number)
And then for each day since the competitor has competed, do this formula, and sum up the points.
Example of how the formula works.
Let’s say there was a competitor who was (hypothetically) WR42 with a time of 6.72 and WR100 at the time (PWR100) was 7.60. Start with 100 points, then divide by 42 bc of the competitor’s World Ranking. (So far 2.38) then take WR100 at the time and divide it by the competitors PR (7.60/6.72 = 1.13) and times by the 2.38 to get 2.69. Then take today’s WR100 (6.09) divided by the WR100 of the selected day (6.09/7.60 = 0.801) and times by the 2.69 to get 2.15 (and round to nearest whole number) to get the number of points that day as 2. Do this for every subsequent day the competitor remains in the top 100, sum those day points up, and that would be the total points awarded to the competitor’s career.
I would also like it if there could be a list where this formula is applied to all competitors, and the competitors are ranked by number of points.
Is anyone willing to help out with this sql request?
I want a query that for a competitior, since the day of their first competition, each day is checked with the following formula.
Note: There will be two notations. PWR100 is WR100 of the day checked, CWR100 is today’s WR100 (as of writing July 2025). P ranking is similar, world rank as of day checked
1. Check if competitor is PWR100 or better
2. If yes, calculate their points, done as follows
2a. 100/(competitor’s P ranking) * (PWR100 time / competitor’s P ranking time) * (CWR100 / PWR100) = Day points (round this number to nearest whole number)
And then for each day since the competitor has competed, do this formula, and sum up the points.
Example of how the formula works.
Let’s say there was a competitor who was (hypothetically) WR42 with a time of 6.72 and WR100 at the time (PWR100) was 7.60. Start with 100 points, then divide by 42 bc of the competitor’s World Ranking. (So far 2.38) then take WR100 at the time and divide it by the competitors PR (7.60/6.72 = 1.13) and times by the 2.38 to get 2.69. Then take today’s WR100 (6.09) divided by the WR100 of the selected day (6.09/7.60 = 0.801) and times by the 2.69 to get 2.15 (and round to nearest whole number) to get the number of points that day as 2. Do this for every subsequent day the competitor remains in the top 100, sum those day points up, and that would be the total points awarded to the competitor’s career.
I would also like it if there could be a list where this formula is applied to all competitors, and the competitors are ranked by number of points.
Is anyone willing to help out with this sql request?
