Team Match Stats Finder - FBref

--------------------------------------

main

Page Setup Time: 0.00321 seconds

SELECT IFNULL(ts.name_short, ts.name) as team_name_abbr, ts.link as team_link, IFNULL(os.name_short, os.name) as opp_name_abbr, opp_team_link, date_local as date, game_id, year_id as year_id_csk, year_id as year_id, IF(home_away_neutral="H","",IF(home_away_neutral="A","@","N")) as game_location, IFNULL(goals_for,0) - IFNULL(goals_against,0) + IFNULL(goals_for,0)/100 + IF(IFNULL(how_winner, opp_how_winner) IN ("score_aet", "kftm_aet", "score_asdet"), (CASE WHEN IFNULL(how_winner, opp_how_winner) = "score_aet" THEN 0.0001 WHEN IFNULL(how_winner, opp_how_winner) = "kftm_aet" THEN 0.0002 WHEN IFNULL(how_winner, opp_how_winner) = "score_asdet" THEN 0.0003 ELSE 0 END), 0) as game_result_csk, CONCAT(IF(win, "W ", IF(loss, "L ", "D ")), goals_for, "-", goals_against) as game_result, sup_games.link as game_link, comp_id, is_big5, "Match Report" as match_report, comp_flag as comp_name_abbr_flag, sup_comps.name AS comp_name_abbr, IF(sup_games.game_of_series, CONCAT(IFNULL(sup_phase_seasons.name_abbr, sup_phase_seasons.name), ", Leg ", sup_games.game_of_series), IFNULL(sup_phase_seasons.name_abbr, sup_phase_seasons.name)) as comp_phase,         IF(sup_games.game_of_series,
  IFNULL(ts_goals_for,0) - IFNULL(ts_goals_against,0) + IFNULL(ts_goals_for,0)/100 +
  IF(is_winner = 1, 0.0001, IF(how_winner IS NOT NULL OR opp_how_winner IS NOT NULL, -0.0001, 0)) + 
  IF(IFNULL(how_winner, opp_how_winner) IN ("away_goals", "aggregate_score_aet", "kftm_aet", "aggregate_score_asdet", "two_wins", "higher_seed", "series_tiebreaker", "aggregate_score"),
  CASE
  WHEN IFNULL(how_winner, opp_how_winner) = "away_goals" AND is_winner = 1    THEN 0.5
  WHEN IFNULL(how_winner, opp_how_winner) = "aggregate_score_aet"             THEN 0.00002
  WHEN IFNULL(how_winner, opp_how_winner) = "kftm_aet"                        THEN 0.00003
  WHEN IFNULL(how_winner, opp_how_winner) = "aggregate_score_asdet"           THEN 0.00004
  WHEN IFNULL(how_winner, opp_how_winner) = "two_wins"                        THEN 0.00005
  WHEN IFNULL(how_winner, opp_how_winner) = "higher_seed"                     THEN 0.00006
  WHEN IFNULL(how_winner, opp_how_winner) = "series_tiebreaker"               THEN 0.00007
  ELSE 0
  END, 0
  ),
  0)
  as tie_result_csk,         IF(sup_games.game_of_series,
  CONCAT(
  IF(is_winner = 1, "W", IF(how_winner IS NOT NULL OR opp_how_winner IS NOT NULL, "L", "")),
  IF(IFNULL(how_winner, opp_how_winner) IN ("away_goals", "aggregate_score_aet", "kftm_aet", "aggregate_score_asdet", "two_wins", "higher_seed", "series_tiebreaker", "aggregate_score"), 
  CONCAT(", ", 
  IFNULL(ts_goals_for,0),
  "-",
  IFNULL(ts_goals_against,0),
  CASE
  WHEN IFNULL(how_winner, opp_how_winner) = "away_goals"              THEN " (away)"
  WHEN IFNULL(how_winner, opp_how_winner) = "aggregate_score_aet"     THEN " (aet)"
  WHEN IFNULL(how_winner, opp_how_winner) = "kftm_aet"                THEN " (pk)"
  WHEN IFNULL(how_winner, opp_how_winner) = "aggregate_score_asdet"   THEN " (sdet)"
  WHEN IFNULL(how_winner, opp_how_winner) = "two_wins"                THEN " (2 of 3)"
  WHEN IFNULL(how_winner, opp_how_winner) = "higher_seed"             THEN " (seed)"
  WHEN IFNULL(how_winner, opp_how_winner) = "series_tiebreaker"       THEN " (series)"
  ELSE ""
  END), "")
  ),
  "")
  as tie_result, CASE WHEN IFNULL(how_winner, opp_how_winner) = "score_aet" THEN " (aet)" WHEN IFNULL(how_winner, opp_how_winner) = "kftm_aet" THEN " (pk)" WHEN IFNULL(how_winner, opp_how_winner) = "score_asdet" THEN " (sdet)" ELSE "" END as game_result_append, goals_for, goals_against, (goals_for - goals_against) AS goal_diff, possession as possession_csk, ROUND(possession,1) AS possession, goals - pens_made AS goals_pens, pens_made, pens_att, (pens_att IS NOT NULL) * pens_att - (pens_made IS NOT NULL) * pens_made AS pens_missed
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, comp_id, phase_id, name_abbr, name_short, name, link, missing_games_opta, games, is_winner, how_winner, goals_for AS ts_goals_for, goals_against AS ts_goals_against FROM sup_team_seasons) as ts USING (team_id, year_id, comp_id, phase_id)
  JOIN (SELECT team_id as opp_team_id, year_id, comp_id, phase_id, name_abbr, name_short, name, link as opp_team_link, how_winner AS opp_how_winner FROM sup_team_seasons) as os USING (opp_team_id, year_id, comp_id, phase_id)
  JOIN sup_comps USING (comp_id)
  JOIN sup_approved_comp_seasons USING (comp_id, year_id)
  JOIN sup_teams USING (team_id)
  LEFT JOIN (SELECT org_id AS governing_org, fifa_id, flag AS comp_flag FROM orgs
  JOIN fifa_members USING (fifa_id)) as comp_flags USING (governing_org)
WHERE sup_games.status IN ('Played', 'Awarded')
  AND year_id>=?
  AND year_id<=?
  AND sup_comps.gender='m'
  AND is_qualifier=?
  AND year_id >= '1988-1989'
  AND sup_comps.age_level='senior'
  AND is_big5=1
  AND date < current_date() 
ORDER BY goals_for DESC, date DESC, team_id LIMIT 0, 20

SQL PARAMS -- $VAR1 = [
          '2025-2026',
          '2025-2026',
          '0'
        ];

Query Time: 0.10 seconds
Table Build Time: 0.05 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'match_status' => 1,
          'year_min' => '2025-2026',
          'match' => 'team_game',
          'year_max' => '2025-2026',
          'comp_gender' => 'm',
          'is_qualifier' => '0',
          'order_by' => 'goals_for',
          'force_year_min' => 1,
          'timeframe' => 'seasons',
          'comp_type' => 'b5'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'year_min' => '2025-2026',
          'match_status' => 1,
          'is_qualifier' => '0',
          'year_max' => '2025-2026',
          'comp_type' => 'b5',
          'force_year_min' => 1
        };