Created
November 1, 2024 11:48
-
-
Save nilsabdi/6021a5a3ea2a747111e818397f56d379 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- The reasoning behind running this continuously is due to how matches with new players' rank are continuously added, so we want to include them in the distribution. | |
| WITH | |
| reference_time AS ( | |
| SELECT TIMESTAMP '2024-10-22 20:00:00+00' AS start_time -- Beginning of all (Deadlock) time, Tuesday at 20:00 GMT (when the first ranks were calculated) | |
| ), | |
| -- The number of weeks since ranked started | |
| current_week AS ( | |
| SELECT | |
| ceil(((EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'GMT') - EXTRACT(EPOCH FROM reference_time.start_time)) / (7 * 24 * 60 * 60))) AS week | |
| FROM reference_time | |
| ), | |
| -- Ranked resets every Tuesday @20:00 GMT, so for any given "ranked week" which runs Tue 20:00 - Tue 20:00, we want to only look at matches within that period of time when evaluating the distribution. | |
| -- https://forums.playdeadlock.com/threads/10-10-2024-update.36958/#post-67710 | |
| last_tuesday AS ( | |
| SELECT | |
| -- Postgres adheres to ISO-8601 where weeks start on Monday. Our "weeks" start on Tuesday @20:00, so we need to adjust for that. | |
| date_trunc('week', CURRENT_DATE) + INTERVAL '1 day 20 hours' | |
| - CASE | |
| WHEN EXTRACT(DOW FROM CURRENT_DATE) < 2 | |
| OR (EXTRACT(DOW FROM CURRENT_DATE) = 2 AND NOW() AT TIME ZONE 'GMT' < CURRENT_DATE + TIME '20:00:00') | |
| THEN INTERVAL '7 days' | |
| ELSE INTERVAL '0 days' | |
| END AS last_tuesday_at_20 | |
| ), | |
| -- Get highest ranked_badge_level for each player on the given ranked match week | |
| player_badges AS ( | |
| SELECT | |
| account_id, | |
| MAX(ranked_badge_level) AS badge_level | |
| FROM match_player | |
| WHERE ranked_badge_level != 0 | |
| AND match_start_time >= (SELECT last_tuesday_at_20 FROM last_tuesday) | |
| GROUP BY account_id | |
| ), | |
| -- Calculate tier and subtier from badge_level | |
| player_ranks AS ( | |
| SELECT | |
| account_id, | |
| badge_level, | |
| FLOOR(GREATEST(LEAST(badge_level, 116), 0) / 10) AS tier, | |
| LEAST(GREATEST(LEAST(badge_level, 116), 0) % 10, 6) AS sub_tier | |
| FROM player_badges | |
| ), | |
| -- Calculate match performance metrics based on various in-game statistics | |
| -- Lower performance_rank is better (1 = best, up to max players in match) | |
| game_performance AS ( | |
| SELECT | |
| mp.account_id, | |
| mp.match_id, | |
| -- Calculate average position (1-N) across 7 different performance metrics | |
| (RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.net_worth DESC) + -- Highest net worth | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.kills DESC) + -- Most kills | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.deaths ASC) + -- Least deaths | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.assists DESC) + -- Most assists | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.player_damage DESC) + -- Highest player damage | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.boss_damage DESC) + -- Highest boss damage | |
| RANK() OVER(PARTITION BY mp.match_id ORDER BY mp.possible_creeps DESC) -- Most creeps | |
| )::float / 7.0 AS avg_performance_position, -- Average position across all metrics (1.0 = best, higher = worse) | |
| m.match_score, | |
| mp.win, | |
| m.region_mode | |
| FROM match_player mp | |
| JOIN match m ON mp.match_id = m.match_id | |
| WHERE mp.source = 'metadata' | |
| AND mp.match_start_time >= (SELECT last_tuesday_at_20 FROM last_tuesday) | |
| AND m.match_mode = 4 | |
| ), | |
| -- Aggregate player statistics and calculate final score | |
| player_stats AS ( | |
| SELECT | |
| gp.account_id, | |
| AVG(gp.avg_performance_position) AS avg_rank, | |
| AVG(gp.match_score) AS avg_match_score, | |
| COUNT(*) FILTER (WHERE NOT gp.win) AS matches_lost, | |
| COUNT(*) FILTER (WHERE gp.win) AS matches_won, | |
| (ARRAY_AGG(gp.region_mode))[1] AS region, | |
| -- Final weighted score (0.0 to 1.0) combines three factors: | |
| ( | |
| 0.05 * AVG(gp.match_score) + -- Match score (5% weight) | |
| 0.05 * (COUNT(*) FILTER (WHERE gp.win)::float / NULLIF(COUNT(*), 0)) + -- Win rate (5% weight) | |
| 0.9 * ((12 - AVG(gp.avg_performance_position)) / 12) -- Performance score (90% weight) | |
| -- Convert position (1-12) to 0-1 scale where 1 is best | |
| ) AS weighted_score | |
| FROM game_performance gp | |
| GROUP BY gp.account_id | |
| ) | |
| -- Final leaderboard | |
| SELECT | |
| (SELECT week FROM current_week) AS week, | |
| ROW_NUMBER() OVER ( | |
| ORDER BY | |
| pr.tier DESC, -- Highest tier first | |
| pr.sub_tier DESC, -- Highest subtier within tier | |
| ps.weighted_score DESC -- Best performance within tier/subtier | |
| ) AS rank, | |
| ps.region, | |
| ps.account_id, | |
| pr.tier, | |
| pr.sub_tier, | |
| LEAST(10, GREATEST(0, ps.weighted_score * 10)) AS form, -- Scale normalized score to 0-10 range | |
| ps.avg_match_score, | |
| ps.avg_rank AS avg_match_ranking, | |
| ps.matches_lost, | |
| ps.matches_won | |
| FROM player_stats ps | |
| JOIN player_ranks pr ON ps.account_id = pr.account_id | |
| ORDER BY rank; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment