Player Match Stats Finder - FBref

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

main

Page Setup Time: 0.00328 seconds

SELECT name_display, name_display_csk, sup_players.link as name_display_link, person_id, age_on_day, IFNULL(ts.name_short, ts.name) as team_name_abbr, ts.link as team_link, opp_team_link, date_local as date, game_id, sup_games.link as game_link, 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, IFNULL(os.name_short, os.name) as opp_name_abbr, pts_position as pos_game, ((games * 2) - games_sub) + (minutes / 1000) as game_started_csk, IF(games_sub=1,"N","Y") as game_started, mod_nationality.fifa_id as nationality_csk, mod_nationality.fifa_id as nationality, is_big5, "Match Report" as match_report, comp_id, 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, comp_flag as comp_name_abbr_flag, minutes, goals, assists, goals + assists AS goals_assists, 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, nationality_flag, CONCAT('/en/country/', fifa_members.fifa_id, '/', REPLACE(fifa_members.name, ' ', '-'), '-Football') AS nationality_link, goals_against
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie, goals_for, goals_against FROM sup_team_games) as tg USING (game_id, team_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, 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)
  LEFT JOIN mod_nationality USING (person_id)
  LEFT JOIN fifa_members USING (fifa_id)
  LEFT JOIN (SELECT person_id, fifa_id, flag AS nationality_flag, name FROM mod_nationality
  JOIN fifa_members USING (fifa_id)) as nationality_flags USING (person_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)
  JOIN (SELECT person_id, team_id, year_id, comp_id, position AS pts_position, missing_games_opta FROM sup_player_team_seasons) as pts USING (person_id, team_id, year_id, comp_id)
WHERE year_id<=?
  AND is_qualifier=?
  AND sup_comps.gender='m'
  AND sup_comps.age_level='senior'
  AND is_big5=1
  AND year_id>=?
  AND year_id >= '1988-1989'
  AND sup_games.status IN ('Played', 'Awarded') 
ORDER BY goals DESC, person_id LIMIT 0, 20

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

Query Time: 1.20 seconds
Table Build Time: 0.06 seconds


--------------------------------------
SH->PARAM -- $VAR1 = {
          'year_max' => '2025-2026',
          'is_qualifier' => '0',
          'comp_gender' => 'm',
          'order_by' => 'goals',
          'weight_type' => 'kgs',
          'comp_type' => 'b5',
          'year_min' => '2025-2026',
          'timeframe' => 'seasons',
          'height_type' => 'height_meters',
          'force_min_year' => 1,
          'match_status' => 1,
          'match' => 'player_game',
          'yob_comp' => 'gt'
        };
SH->PARAM_NO_DEFAULT -- $VAR1 = {
          'comp_type' => 'b5',
          'year_min' => '2025-2026',
          'is_qualifier' => '0',
          'force_min_year' => 1,
          'match_status' => 1,
          'year_max' => '2025-2026'
        };