Ranking Query Is Too Large

[sipacate]

I've got a ranking query that ranks the performance of teams in challenges.

The hierarchy of data is as follows:
teams have members
members have activities
activities have activitytypes
challenges have activitytypes

If I want to rank the performance all teams in a single challenge, this query works great:

SELECT 	t.teamID, t.teamName, 
		scoring.challengeID, 
		outerchallenge.name AS ChallengeName, outerchallenge.description AS ChallengeDescription, outerchallenge.startDate, outerchallenge.endDate, 
		scoring.standardValueSum, scoring.standardUnit, scoring.rank 
FROM challenge outerchallenge 
	LEFT JOIN ( 
		SELECT teamID, challengeID, standardValueSum, standardUnit, FIND_IN_SET(standardValueSum, scores ) AS rank 
		FROM ( 
			SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum, v.standardUnit 
			FROM v_activitystats v 
				INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
				INNER JOIN teammember ON v.memberID = teammember.memberID 
				INNER JOIN challenge c ON mc.challengeID = c.challengeID 
				INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
			WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
				AND c.challengeID = 33  
			GROUP BY standardUnit, challengeID, teamID 
			) vstats 
	CROSS JOIN ( 
		SELECT GROUP_CONCAT( DISTINCT standardValueSum ORDER BY standardValueSum DESC ) AS scores 
		FROM ( 
			SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum 
			FROM v_activitystats v 
				INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
				INNER JOIN teammember ON v.memberID = teammember.memberID 
				INNER JOIN challenge c ON mc.challengeID = c.challengeID 
				INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
			WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
				AND c.challengeID = 33  
			GROUP BY challengeID, teamID 
			) vstats 
		) scores 
	) scoring 
	
	ON outerchallenge.challengeID = scoring.challengeID 
		INNER JOIN team t ON scoring.teamID = t.teamID

ChallengeID, Team, Ranking
99 Red Team 1
99 Blue Team 2

Once again, this works just fine for a specific challenge, (ID = 33)



I want to get a query with the same sort of rankings, but for multiple challenges, like those that have already ended.

I tried this query:

SELECT rankings.teamID, stuff.teamName, rankings.challengeID, 
		rankings.ChallengeName, rankings.ChallengeDescription, rankings.startDate, rankings.endDate, 
		rankings.standardValueSum, rankings.standardUnit, rankings.rank 
FROM challenge chal 
	LEFT JOIN ( 
	SELECT t.teamID, t.teamName, scoring.challengeID, 
		outerchallenge.name AS ChallengeName, outerchallenge.description AS ChallengeDescription, outerchallenge.startDate, outerchallenge.endDate, 
		scoring.standardValueSum, scoring.standardUnit, scoring.rank 
	FROM challenge outerchallenge 
		LEFT JOIN ( 
			SELECT teamID, challengeID, standardValueSum, standardUnit, FIND_IN_SET(standardValueSum, scores ) AS rank 
			FROM ( 
				SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum, v.standardUnit 
				FROM v_activitystats v 
					INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
					INNER JOIN teammember ON v.memberID = teammember.memberID 
					INNER JOIN challenge c ON mc.challengeID = c.challengeID 
					INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
				WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
				GROUP BY standardUnit, challengeID, teamID ) vstats 
			CROSS JOIN ( 
				SELECT GROUP_CONCAT( DISTINCT standardValueSum ORDER BY standardValueSum DESC ) AS scores 
				FROM ( 
					SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum 
					FROM v_activitystats v 
						INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
						INNER JOIN teammember ON v.memberID = teammember.memberID 
						INNER JOIN challenge c ON mc.challengeID = c.challengeID 
						INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
					WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
					GROUP BY challengeID, teamID 
				) vstats 
			) scores 
		) scoring ON outerchallenge.challengeID = scoring.challengeID 
		INNER JOIN team t ON scoring.teamID = t.teamID 
) rankings ON chal.challengeID = rankings.challengeID
WHERE chal.endDate <= current_date()

But rather than every challenge having a 1st place, and a 2nd place, the rankings are across all of the challenges. Like this

ChallengeID, Team, Ranking
99 Red Team 1
99 Blue Team 2
134 Red Team 3
134 Blue Team 4
443 Red Team 5
442 Blue Team 6


So, I suppose, I'm evaluating the ranking at the wrong place, but I'm sort of out of ideas for how to make this work. How can I get results like this:
ChallengeID, Team, Ranking
99 Red Team 1
99 Blue Team 2
134 Red Team 1
134 Blue Team 2
443 Red Team 1
443 Blue Team 2