Open Shreyas15srivatsa opened 1 year ago
This requires dev work on both stats service and mantle. Let's split the ticket into two, with this ticket covering stats service.
@sid42
------
-- PER GAME STATS
------
-- TOTAL RUSHING YARDS --
WITH home_rush_yards AS (
SELECT SUM(r.yard) AS rush_yards
FROM gridironrushes r
INNER JOIN gridirongameevents e ON r.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.home AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
), away_rush_yards AS (
SELECT SUM(r.yard) AS rush_yards
FROM gridironrushes r
INNER JOIN gridirongameevents e ON r.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.away AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
)
SELECT COALESCE((SELECT * FROM home_rush_yards), 0) AS rush_yards
UNION ALL
SELECT COALESCE((SELECT * FROM away_rush_yards), 0) AS rush_yards;
-- TOTAL PASSING YARDS --
WITH home_pass_yards AS (
SELECT SUM(t.yard) AS pass_yards
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.home AND t.result != 'miss' AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
), away_pass_yards AS (
SELECT SUM(t.yard) AS pass_yards
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.away AND t.result != 'miss' AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
)
SELECT COALESCE((SELECT * FROM home_pass_yards), 0) AS pass_yards
UNION ALL
SELECT COALESCE((SELECT * FROM away_pass_yards), 0) AS pass_yards;
-- AVG YARDS PER PLAY --
WITH total_non_miss_yards_home AS (
SELECT COUNT(g.id) AS play_count, SUM(r.yard) as total_yards
FROM gridironrushes r
INNER JOIN gridirongameevents e ON r.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.home AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
UNION ALL
SELECT COUNT(g.id) AS play_count, SUM(t.yard) as total_yards
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.home AND t.result != 'miss' AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
),
total_non_miss_yards_away AS (
SELECT COUNT(g.id) AS play_count, SUM(r.yard) as total_yards
FROM gridironrushes r
INNER JOIN gridirongameevents e ON r.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.away AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
UNION ALL
SELECT COUNT(g.id) AS play_count, SUM(t.yard) as total_yards
FROM gridironthrows t
INNER JOIN gridirongameevents e ON t.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.away AND t.result != 'miss' AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
)
SELECT COALESCE((SELECT ROUND(SUM(total_yards)::numeric / SUM(play_count), 2)), 0)::numeric as avg_yards_per_play
FROM total_non_miss_yards_home
UNION ALL
SELECT COALESCE((SELECT ROUND(SUM(total_yards)::numeric / SUM(play_count), 2)), 0)::numeric as avg_yards_per_play
FROM total_non_miss_yards_away;
-- TOTAL TOUCHDOWNS --
WITH all_touchdowns_events AS (
SELECT eventId
FROM gridironrushes
WHERE result = 'touchdown'
UNION ALL
SELECT eventId
FROM gridironthrows
WHERE result = 'touchdown'
),
touchdowns_home AS (
SELECT COUNT(g.id) AS touchdowns
FROM all_touchdowns_events td
INNER JOIN gridirongameevents e ON td.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.home AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
),
touchdowns_away AS (
SELECT COUNT(g.id) AS touchdowns
FROM all_touchdowns_events td
INNER JOIN gridirongameevents e ON td.eventId = e.id
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE e.teamFor = g.away AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
)
SELECT COALESCE((SELECT touchdowns), 0)::numeric as touchdowns
FROM touchdowns_home
UNION ALL
SELECT COALESCE((SELECT touchdowns), 0)::numeric as touchdowns
FROM touchdowns_away;
-- TOTAL TURNOVERS --
WITH home_turnovers AS (
SELECT COUNT(e.id) AS turnovers
FROM gridirongameevents e
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE playType = 'turnover' AND e.teamFor = g.home AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.home
), away_turnovers AS (
SELECT COUNT(e.id) AS turnovers
FROM gridirongameevents e
INNER JOIN gridirongames g ON e.gameId = g.id
WHERE playType = 'turnover' AND e.teamFor = g.away AND e.gameId = 'dde41514-e573-42ba-86de-32d48a4bdda8'
GROUP BY g.away
)
SELECT COALESCE((SELECT * FROM home_turnovers), 0) AS turnovers
UNION ALL
SELECT COALESCE((SELECT * FROM away_turnovers), 0) AS turnovers;
Do these stats for both home and away teams: