<commit_before>Average damage of players of [class] in highest division from all leagues

SELECT AVG(PlaysFormat.damagepermin) FROM (
	SELECT playson.player,LD.league FROM (
		SELECT * FROM TeamDivision as TD
		INNER JOIN LeagueDivision as LD
		ON TD.division=LD.division
		WHERE LD.rank=1 ) AS TopTeams
	INNER JOIN PlaysOn ON TopTeams.team=PlaysOn.team ) AS TopPlayers
INNER JOIN PlaysFormat ON PlaysFormat.playerID=TopPlayers.player
WHERE PlaysFormat.class = 'demoman'
GROUP BY LD.league


Compare performance of medics between formats (Performance = HealsPerMin, Ubers, Drops)

SELECT PF.format, AVG(PF.healsPerMin) as avg_HPM, AVG(PF.ubers/PF.drops) as avg_UD_rate
FROM PlaysFormat PF
WHERE class='medic' AND PF.drops <> 0
GROUP BY PF.format;


Correlation between damage per minute of demoman and heals per minute of medic

SELECT (AVG(Med.healsPerMin*Dem.damagePerMin)-AVG(Med.healsPerMin)*AVG(Dem.damagePerMin)/(STDDEV(Med.healsPerMin)*STDDEV(Dem.damagePerMin)))
FROM (SELECT * FROM PlaysOn,PlaysFormat WHERE PlaysOn.player=PlaysFormat.player AND PlaysFormat.class="medic")as Med, 
	(SELECT * FROM PlaysOn,PlaysFormat WHERE PlaysOn.player=PlaysFormat.player AND PlaysFormat.class="demoman")as Dem
WHERE Med.team=Dem.team <commit_msg>Completed and tested the third query.<commit_after>Average damage of players of [class] in highest division from all leagues

SELECT AVG(PlaysFormat.damagepermin) FROM (
	SELECT playson.player,LD.league FROM (
		SELECT * FROM TeamDivision as TD
		INNER JOIN LeagueDivision as LD
		ON TD.division=LD.division
		WHERE LD.rank=1 ) AS TopTeams
	INNER JOIN PlaysOn ON TopTeams.team=PlaysOn.team ) AS TopPlayers
INNER JOIN PlaysFormat ON PlaysFormat.playerID=TopPlayers.player
WHERE PlaysFormat.class = 'demoman'
GROUP BY LD.league


Compare performance of medics between formats (Performance = HealsPerMin, Ubers, Drops)

SELECT PF.format, AVG(PF.healsPerMin) as avg_HPM, AVG(PF.ubers/PF.drops) as avg_UD_rate
FROM PlaysFormat PF
WHERE class='medic' AND PF.drops <> 0
GROUP BY PF.format;


Correlation between damage per minute of demoman and heals per minute of medic

SELECT 	AVG(Med.healsPerMin) AS HPM_avg, 
	AVG(Dem.damagePerMin) AS DMP_avg, 
	STDDEV(Med.healsPermin) AS HPM_dev, 
	STDDEV(Dem.damagePerMin) AS DPM_dev, 
	(AVG(Med.healsPerMin*Dem.damagePerMin)-AVG(Med.healsPerMin)*AVG(Dem.damagePerMin))/STDDEV(Dem.damagePerMin)/STDDEV(Med.healsPerMin) AS cov_coeff
FROM    (SELECT PlaysOn.team AS team, PlaysFormat.healsPerMin AS healsPerMin 
	FROM PlaysOn,PlaysFormat 
	WHERE PlaysOn.player=PlaysFormat.player AND PlaysFormat.class='medic'
	)AS Med,
	(SELECT PlaysOn.team AS team, PlaysFormat.damagePerMin AS damagePerMin 
	FROM PlaysOn,PlaysFormat 
	WHERE PlaysOn.player=PlaysFormat.player AND PlaysFormat.class='demoman'
	) AS Dem
WHERE Med.team=Dem.team AND Med.healsPerMin IS NOT NULL AND Dem.damagePerMin IS NOT NULL;
{'fraction_non_alphanumeric': 0.0824022346368715, 'fraction_numerical': 0.0013966480446927375, 'mean_word_length': 5.61662817551963, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1149090', 'n_tokens_mistral': 1069, 'n_tokens_neox': 985, 'n_words': 273}