Function getAdvancedStats

app/stats/stats.service.js:144–635

(
    year,
    team,
    excludeGarbageTime,
    startWeek,
    endWeek
  )

  };

  const getAdvancedStats = async (
    year,
    team,
    excludeGarbageTime,
    startWeek,
    endWeek
  ) => {
    let filter = "WHERE ";
    let params = [];
    let index = 1;

    if (year) {
      filter += `g.season = $${index}`;
      params.push(year);
      index++;
    }

    if (team) {
      filter += ` ${year ? "AND " : ""}LOWER(t.school) = LOWER($${index})`;
      params.push(team);
      index++;
    }

    if (startWeek) {
      filter += ` AND (g.week >= $${index} OR g.season_type = 'postseason')`;
      params.push(startWeek);
      index++;
    }

    if (endWeek) {
      filter += ` AND g.week <= $${index} AND g.season_type <> 'postseason'`;
      params.push(endWeek);
      index++;
    }

    const mainTask = db.any(
      `
        WITH plays AS (
            SELECT  g.id,
                    g.season,
                    t.school,
                    p.drive_id,
                    p.down,
                    p.distance,
                    p.yards_gained,
                    c.name AS conference,
                    CASE
                        WHEN p.offense_id = t.id THEN 'offense'
                        ELSE 'defense'
                    END AS o_d,
                    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