[Stats] Calculate Basketball per game stats

[Shreyas15srivatsa]

Do these stats for both home and away teams:

• Field goal percentage (Total 2pts and 3pts made by a team in a game divided by Total 2pts and 3pts shots taken by a team in a game)
• 3pt percentage (Total 3pts made by a team in a game divided by Total 3pts shots taken by a team in a game)
• Free throws made (Total 1pt shots made by a team in a game divided Total 1pt shots taken by a team in a game)
• Total turnovers by a team
• Total steals by a team

[Shreyas15srivatsa]

This requires dev work on both stats service and mantle. Let's split the ticket into two, with this ticket covering stats service.

[babrar]

@sid42 These queries should return all the relevent per-game stats. All the queries below return a tuple of the form (home_metric, away_metric). Requires you to know gameId before making the queries.

-----------------
-- PER-GAME STATS
-----------------

-- FIELD GOAL PERCENTAGE --
WITH home_fgp AS (
    SELECT g.home, ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS fgp
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON e.id = p.eventId
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.home AND g.id = '{gameId}'
    GROUP BY g.home
), away_fgp AS (
    -- Away team's field goal % for game.
    SELECT g.away, ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS fgp
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON e.id = p.eventId
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.away AND g.id = '{gameId}'
    GROUP BY g.away
)
SELECT COALESCE((SELECT fgp FROM home_fgp), 0) AS field_goal_percentage
UNION ALL
SELECT COALESCE((SELECT fgp from away_fgp), 0) AS field_goal_percentage;

-- 3PT PERCENTAGE --
WITH home_3pt_pct AS (
    SELECT ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS three_points_pct
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON p.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE p.point = 3 AND e.teamFor = g.home AND g.id = '{gameId}'
    GROUP BY g.home
), away_3pt_pct AS (
    SELECT ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS three_points_pct
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON p.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE p.point = 3 AND e.teamFor = g.away AND g.id = '{gameId}'
    GROUP BY g.away
)
SELECT COALESCE((SELECT three_points_pct FROM home_3pt_pct), 0) AS three_points_percentage
UNION ALL
SELECT COALESCE((SELECT three_points_pct FROM away_3pt_pct), 0) AS three_points_percentage;

-- FREE THROW PERCENTAGE --
WITH home_free_throw_pct AS (
    SELECT ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS free_throw_pct
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON p.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE p.point = 1 AND e.teamFor = g.home AND g.id = '{gameId}'
    GROUP BY g.home
), away_free_throw_pct AS (
    SELECT ROUND(COUNT(CASE WHEN p.result = 'made' then 1 else NULL end)::numeric / COUNT(p.id), 2) AS free_throw_pct
    FROM basketballpoints p
    INNER JOIN basketballgameevents e ON p.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE p.point = 1 AND e.teamFor = g.away AND g.id = '{gameId}'
    GROUP BY g.away
)
SELECT COALESCE((SELECT free_throw_pct FROM home_free_throw_pct), 0) AS free_throw_percentage
UNION ALL
SELECT COALESCE((SELECT free_throw_pct FROM away_free_throw_pct), 0) AS free_throw_percentage;

-- TOTAL TURNOVERS --
WITH home_turnovers AS (
    SELECT COUNT(t.id) AS turnovers
    FROM basketballturnovers t
    INNER JOIN basketballgameevents e ON t.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.home AND g.id = '{gameId}'
    GROUP BY g.home
), away_turnovers AS (
    SELECT COUNT(t.id) AS turnovers
    FROM basketballturnovers t
    INNER JOIN basketballgameevents e ON t.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.away AND g.id = '{gameId}'
    GROUP BY g.away
)
SELECT COALESCE((SELECT * FROM home_turnovers), 0) AS turnovers
UNION ALL
SELECT COALESCE((SELECT * FROM away_turnovers), 0) AS turnovers;

-- TOTAL STEALS --
WITH home_steals AS (
    SELECT COUNT(s.id) AS steals
    FROM basketballsteals s
    INNER JOIN basketballgameevents e ON s.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.home AND g.id = '{gameId}'
    GROUP BY g.home
), away_steals AS (
    SELECT COUNT(s.id) AS steals
    FROM basketballsteals s
    INNER JOIN basketballgameevents e ON s.eventId = e.id
    INNER JOIN basketballgames g ON e.gameId = g.id
    WHERE e.teamFor = g.away AND g.id = '{gameId}'
    GROUP BY g.away
)
SELECT COALESCE((SELECT * FROM home_steals), 0) AS steals
UNION ALL
SELECT COALESCE((SELECT * FROM away_steals), 0) AS steals;

[babrar]

PR --> #38