rankings.md

Rankings notes

Keeping track of stuff while developing the rankings (monthly high scores, fighters of the month, hall of queens)

Monthly rollover

All leaderboard stats are based on monthly scores - a player's all-time wins and points are available but never used for the leaderboard.

To generate monthly records (technique for doing ranking in SQL is from http://www.1keydata.com/sql/sql-rank.html):

# Copy players' monthly scores to MonthlyScores table and compute ranking by points (example query for jan 1, 2015)
INSERT INTO MonthlyScores (id, record_month, month_points, month_games, month_wins, month_wins_solo, month_points_rank, month_win_pct)
SELECT a1.id, "2014-12", a1.month_points, a1.month_games, a1.month_wins, a1.month_wins_solo, count(a2.month_points) month_points_rank, (a1.month_wins / a1.month_games)
	FROM Users a1, Users a2
	WHERE a1.month_games > 0 AND a2.month_games > 0 AND (a1.month_points < a2.month_points OR (a1.month_points=a2.month_points AND a1.id = a2.id))
	GROUP BY a1.id, a1.month_points
ON DUPLICATE KEY UPDATE record_month = record_month	

# Then reset players' monthly scores to 0 for next month
UPDATE Users 
SET month_points=0, month_games=0, month_wins=0, month_wins_solo=0

Current High Scores

Show players ranked 1-100 by points earned this month. (There may be more than 100 players on the list if multiple players are tied for rank 100). The display is ranked by points by default; the list can be re-ordered by win percentage.

(Distinction: in ranking by points, two players with the same points will have the same rank. In sortin)

The high score column also shows monthly stats for the currently logged-in player, even if that player is not in the top 100.

Current High Scores are generated by Rankings.thisMonth() with a query approximately like this:

SELECT `id`, `nickname`, `avatar`, 
	`month_points`, `month_games`, `month_wins`, `month_wins_solo`, 
	`alltime_points`, `alltime_games`, `alltime_wins`, `alltime_wins_solo`, 
	ROUND(100 * month_wins / month_games) as `month_win_pct`, 
	ROUND(100 * alltime_wins / alltime_games) as `alltime_win_pct`, 
	ROUND(100 * alltime_wins_solo / alltime_games) as `alltime_win_solo_pct`, 
	ROUND(100 * (alltime_wins-alltime_wins_solo) / alltime_games) as `alltime_win_team_pct` 
FROM `Users` 
WHERE `Users`.`month_games` > 0 
ORDER BY `month_points` DESC LIMIT 100;

Current user's stats are generated by Rankings.thisMonthUserInfo(), something like this:

SELECT a1.id, a1.nickname, a1.avatar, 
	a1.month_points, a1.month_games, a1.month_wins, a1.month_wins_solo, 
	a1.alltime_points, a1.alltime_games, a1.alltime_wins, a1.alltime_wins_solo, 
	ROUND(100 * a1.month_wins / a1.month_games) AS month_win_pct, 
	ROUND(100 * a1.alltime_wins / a1.alltime_games) AS alltime_win_pct, 
	ROUND(100 * a1.alltime_wins_solo / a1.alltime_games) AS alltime_win_solo_pct, 
	ROUND(100 * (a1.alltime_wins-a1.alltime_wins_solo) / a1.alltime_games) AS alltime_win_team_pct, 
	COUNT(a2.nickname) points_rank 
FROM Users a1, Users a2 
WHERE a1.id=:user_id AND (a1.month_points < a2.month_points OR (a1.month_points=a2.month_points AND a1.id = a2.id) ) 
GROUP BY a1.month_points 

SISSYFiGHTERs of the Month

At the end of each month, the sissyfighters-of-the-month (SoM) are chosen: take the players ranked up to 25 by points earned that month (could be more than 25 players if there's a tie), then rank by win percentage. We might increase the size of the SoM list in the future if more people start playing the game.

Each player who makes it into the SoM list earns fame points based on their position in the SoM ranks: 25 points for 1st place, 24 for 2nd place, etc. We need to store the points in the table rather than computing them on the fly, because if we change the size of the SoM list in the future, the point structure will also change (e.g. if we expand the list to 50, it will be 50 points for 1st place down to 1 point for 50th place).

# for testing to see what the top 25 looks like (not ranked)
SELECT * FROM (
	SELECT month_points, month_points_rank, month_games, month_wins, month_win_pct FROM MonthlyScores 
	WHERE record_month="2014-12" AND month_points_rank > 0 AND month_points_rank <= 25
) topByPoints
ORDER BY month_win_pct DESC

# for updating the records with rank-by-win-percent for those whose rank-by-points is up to 25:
UPDATE MonthlyScores,
(
	SELECT a1.id as rank_id, count(a2.month_win_pct) as computed_rank
	FROM 
		(SELECT id, month_win_pct from MonthlyScores
			WHERE month_points_rank > 0 AND month_points_rank <= 25
				AND record_month = "2014-12" 
		) a1, 
		(SELECT id, month_win_pct from MonthlyScores
			WHERE month_points_rank > 0 AND month_points_rank <= 25
				AND record_month = "2014-12" 
		) a2
	WHERE a1.month_win_pct <= a2.month_win_pct OR (a1.month_win_pct = a2.month_win_pct AND a1.id = a2.id)
	GROUP BY a1.id, a1.month_win_pct
	ORDER BY a1.month_win_pct DESC, a1.id DESC
) rankedTable
SET month_win_pct_rank = computed_rank, month_fame_points = 26 - computed_rank
WHERE id = rank_id
	AND record_month = "2014-12"

Hall of Queens

The Hall of Queens displays the top 100(?) players ordered by the sum of their fame points over all months.