rank_weight.sql

#new ladder expression
SELECT
	username,
	recruits,
	soldiers,
	gold,
	(recruits + soldiers + scientists + spies) AS unitTotal,
	(SELECT COUNT(*) FROM pastCombat WHERE (attackerId = accounts.id AND victor = 'attacker') OR (defenderId = accounts.id AND victor = 'defender')) AS successfulCombats,
	(
		(recruits + soldiers + scientists + spies) +
		(SELECT COUNT(*) FROM pastCombat WHERE (attackerId = accounts.id AND victor = 'attacker') OR (defenderId = accounts.id AND victor = 'defender')) / 30 +
		gold / 10
	) as rankWeight
FROM
	accounts JOIN profiles ON accounts.id = profiles.accountId
ORDER BY 
	rankWeight DESC
;