[Stats] Calculate league-wide stats for Basketball

[KnotScientific]

• [ ] Top 5 Players by Points Per Game (Total points by a player divided by Games Played)
• [ ] Top 5 Players by Rebounds Per Game (Total Rebounds by a player divided by Games Played)
• [ ] Top 5 Players by Assists Per Game (Total Assists by a player divided by Games Played)
• [ ] Top 5 Players by Blocks Per Game (Total Blocks by a player divided by Games Played)
• [ ] Top 5 Players by Steals Per Game (Total Steals by a player divided by Games Played)
• [ ] Top 5 Players by Field Goal Percentage (Total 3pt and 2pt shots combined made by a player divided by Total 3pt and 2pt shots taken by a player
• [ ] Top 5 Players by 3pt Percentage (Total 3pt shots made by a player divided by Total 3pt shots taken by a player)
• [ ] Top 5 Players by Free Throw Percentage (Total 1pt shots made by a player divided by Total 1pt shots taken by a player)

[babrar]

@sid42 these queries should give you what you need. EDIT: Updated to reflect "per-league" logic

-- league-wide stats
-- top 5 points per game
SELECT p.player, ROUND(SUM(p.point)::numeric / COUNT(DISTINCT g.id), 2) AS points_per_game 
FROM basketballpoints p
INNER JOIN basketballgameevents e ON p.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE p.result = 'made' AND l.id = '{leagueId}'
GROUP BY p.player
ORDER BY points_per_game DESC
LIMIT 5;

-- top 5 rebounds per game
SELECT r.player, ROUND(COUNT(r.player)::numeric / COUNT(DISTINCT g.id), 2) AS rebounds_per_game 
FROM basketballrebounds r
INNER JOIN basketballgameevents e ON r.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
GROUP BY r.player AND l.id = '{leagueId}'
ORDER BY rebounds_per_game DESC
LIMIT 5;

-- top 5 blocks per game
SELECT b.player, ROUND(COUNT(b.player)::numeric / COUNT(DISTINCT g.id), 2) AS blocks_per_game 
FROM basketballblocks b
INNER JOIN basketballgameevents e ON b.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
GROUP BY b.player AND l.id = '{leagueId}'
ORDER BY blocks_per_game DESC
LIMIT 5;

-- top 5 steals per game
SELECT s.player, ROUND(COUNT(s.player)::numeric / COUNT(DISTINCT g.id), 2) AS steals_per_game 
FROM basketballsteals s
INNER JOIN basketballgameevents e ON s.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE l.id = '{leagueId}'
GROUP BY s.player
ORDER BY steals_per_game DESC
LIMIT 5;

-- top 5 assists per game
SELECT p.player, ROUND(COUNT(p.id)::numeric / COUNT(DISTINCT g.id), 2) AS assists_per_game 
FROM basketballpoints p
INNER JOIN basketballgameevents e ON p.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE p.result = 'made' AND p.assist IS NOT NULL AND l.id = '{leagueId}'
GROUP BY p.player
ORDER BY assists_per_game DESC
LIMIT 5;

-- field goal pct
SELECT p.player, ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS field_goal_percentage
FROM basketballpoints p
INNER JOIN basketballgameevents e ON e.id = p.eventId
INNER JOIN basketballgames g ON g.id = e.gameId
INNER JOIN leagues l ON g.leagueId = l.id
WHERE l.id = '{leagueId}'
GROUP BY p.player
ORDER by field_goal_percentage DESC
LIMIT 5;

-- 3pt percentage
SELECT p.player, ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS three_points_percentage
FROM basketballpoints p
INNER JOIN basketballgameevents e ON p.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE p.point = 3 AND l.id = '{leagueId}'
GROUP BY p.player
ORDER by three_points_percentage DESC
LIMIT 5;

-- query above will not return name of any players who hasn't attempted any 3 pt shots
-- is that okay?

-- free throw percentage
SELECT p.player,ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else 0 end)::numeric / COUNT(p.id), 2) as free_throw_percentage
FROM basketballpoints p
INNER JOIN basketballgameevents e ON p.eventId = e.id
INNER JOIN basketballgames g ON e.gameId = g.id
INNER JOIN leagues l ON g.leagueId = l.id
WHERE p.point = 1 AND l.id = '{leagueId}'
GROUP BY p.player
ORDER by free_throw_percentage DESC
LIMIT 5;

-- query above will not return name of any players who hasn't attempted any 1 pt shots
-- is that okay?

[sid42]

Huge

[Shreyas15srivatsa]

Legend @babrar, big up 🔝

[babrar]

@sid42 before using these queries, lmk. I'll need to update them a bit.

[babrar]

@sid42 Updated

[babrar]

PR -> #38