Function getAdvancedBoxScore

app/stats/stats.service.js:888–1447

(id)

  };

  const getAdvancedBoxScore = async (id) => {
    const teamTask = db.any(
      ` 
            WITH havoc AS (
                WITH fumbles AS (
                    SELECT t.school, COALESCE(SUM(CAST(s.stat AS NUMERIC)), 0.0) AS fumbles
                    FROM game AS g
                        INNER JOIN game_team AS gt ON g.id = gt.game_id
                        INNER JOIN team AS t ON gt.team_id = t.id
                        LEFT JOIN game_player_stat AS s ON s.game_team_id = gt.id AND s.type_id = 4 AND s.category_id = 10
                    WHERE g.id = $1
                    GROUP BY t.school
                )
                SELECT 	t.school,
                        (COALESCE(SUM(CAST(s.stat AS NUMERIC)), 0.0) + fumbles) AS total_havoc,
                        COALESCE(SUM(CAST(s.stat AS NUMERIC)) FILTER (WHERE s.type_id IN (16,24)), 0.0) AS db_havoc,
                        (COALESCE(SUM(CAST(s.stat AS NUMERIC)) FILTER (WHERE s.type_id = 21), 0.0) + f.fumbles) AS front_seven_havoc
                FROM game AS g
                    INNER JOIN game_team AS gt ON g.id = gt.game_id
                    INNER JOIN game_team AS gt2 ON g.id = gt2.game_id AND gt.id <> gt2.id
                    INNER JOIN team AS t ON gt.team_id = t.id
                    INNER JOIN game_team_stat AS s ON s.game_team_id = gt.id AND s.type_id IN (16,21,24)
                    LEFT JOIN fumbles AS f ON t.school <> f.school
                WHERE g.id = $1
                GROUP BY t.school, f.fumbles
            ), plays AS (
                SELECT  g.id,
                        g.season,
                        g.week,
                        t.school,
                        g.excitement,
                        gt.win_prob,
                        gt.home_away AS home_away,
                        gt.points AS points,
                        gt.winner AS winner,
                        CASE
                            WHEN p.down = 2 AND p.distance >= 8 THEN 'passing'
                            WHEN p.down IN (3,4) AND p.distance >= 5 THEN 'passing'
                            ELSE 'standard'
                        END AS down_type,
                        CASE
                            WHEN p.play_type_id IN (20,26,34,36,37,38,39,63) THEN false
                            WHEN p.scoring = true THEN true
                            WHEN p.down = 1 AND (CAST(p.yards_gained AS NUMERIC) / p.distance) >= 0.5 THEN true
                            WHEN p.down = 2 AND (CAST(p.yards_gained AS NUMERIC) / p.distance) >= 0.7 THEN true
                            WHEN p.down IN (3,4) AND (p.yards_gained >= p.distance) THEN true
                            ELSE false
                        END AS success,
                        CASE 
                            WHEN p.play_type_id IN (3,4,6,7,24,26,36,51,67) THEN 'Pass'
                            WHEN p.play_type_id IN (5,9,29,39,68) THEN 'Rush'
                            ELSE 'Other'
                        END AS play_type,
                        CASE
                            WHEN p.period = 2 AND p.scoring = false AND ABS(p.home_score - p.away_score) > 38 THEN true
                            WHEN p.period = 3 AND p.scoring = false AND ABS(p.home_score - p.away_score) > 28 THEN true
                            WHEN p.period = 4 AND p.scoring = false AND ABS(p.home_score - p.away_score) > 22 THEN true
                            WHEN p.period = 2 AND p.scoring = true AND ABS(p.home_score - p.away_score) > 45 THEN true