[Stats] Calculate league-wide stats for Gridiron/Football

[KnotScientific]

• [ ] Top 5 players by Total rushing yards (GridironRushes table, calculate total yards for a player)
• [ ] Top 5 players by Total receiving yards (Total yards by a playerReceiving in all GridironThrows table)
• [ ] Top 5 players by Total throwing yards (Total non-missed yards by a playerThrowing in all GridironThrows table). Basically count the yards as part of the total player as long as the result isn’t “Miss”
• [ ] Top 5 players by Total kicks made (Count all the non-missed kicks by a player in the GridironKicks table)
• [ ] Top 5 players by Completion percentage (Total throws in GridironThrows table by a player where result isn’t “Miss” divided by Total throws in GridironThrows table by a player)

[babrar]

@sid42

------
-- PER LEAGUE STATS
------
-- TOP 5 PLAYERS BY TOTAL RUSHING YARDS -> (player: str, rushes: int) x5
SELECT r.player, SUM(r.yard) AS total_rushing_yards 
FROM gridironrushes r
INNER JOIN gridirongameevents e ON r.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE l.id = 'ea4bc412-c6e3-40e9-9753-275730216d50'
GROUP BY r.player
ORDER BY total_rushing_yards DESC
LIMIT 5;

-- TOP 5 PLAYERS BY TOTAL RECEIVING YARDS -> (player: str, yards: int) x5
SELECT t.playerReceiving, SUM(t.yard) AS total_receiving_yards 
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE t.result!= 'miss' AND l.id = 'ea4bc412-c6e3-40e9-9753-275730216d50'
GROUP BY t.playerReceiving
ORDER BY total_receiving_yards DESC
LIMIT 5;

-- TOP 5 PLAYERS BY TOTAL THROWING YARDS -> (player: str, yards: int) x5
SELECT t.playerThrowing, SUM(t.yard) AS total_throwing_yards 
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE t.result!= 'miss' AND l.id = 'ea4bc412-c6e3-40e9-9753-275730216d50'
GROUP BY t.playerThrowing
ORDER BY total_throwing_yards DESC
LIMIT 5;

-- TOP 5 PLAYERS BY THROW COMPLETION PERCENTAGE -> (player: str, percentage: float) x5
SELECT t.playerThrowing, ROUND(COUNT(CASE WHEN t.result != 'miss' THEN 1 ELSE NULL END)::numeric / COUNT(t.playerThrowing), 2) AS throw_completion_pct 
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE l.id = 'ea4bc412-c6e3-40e9-9753-275730216d50'
GROUP BY t.playerThrowing
ORDER BY throw_completion_pct DESC
LIMIT 5;

-- TOP 5 PLAYERS BY TOTAL KICKS -> (player: str, kicks: int) x5
SELECT k.player, COUNT(k.player) AS total_kicks 
FROM gridironkicks k
INNER JOIN gridirongameevents e ON k.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE k.result != 'miss' AND l.id = 'ea4bc412-c6e3-40e9-9753-275730216d50'
GROUP BY k.player
ORDER BY total_kicks DESC
LIMIT 5;