MYSQL Query Wizard Needed - Partition issue with shared IDs

Question

I'm having an issue with a sports stats script I'm writing which queries a MySQL database. The aim is to return the stats for the last 6 fixtures for each team.

The fixtures query successfully returns the last 6 fixtures for each team. As the teams involved have often played an uneven number of fixtures at any point in the season, querying the statistics table using those fixture IDs returns inconsistent results.

As an example, using two teams, West Ham United and Newcastle United. These are the last 6 fixtures for those teams in reverse chronological order.

West Ham United:

1. v Everton
2. @ Tottenham Hotspur
3. v Aston Villa
4. @ Liverpool
5. v Wolverhampton Wanderers
6. v Newcastle United

Newcastle United:

1. @ Tottenham Hotspur
2. @ Everton
3. @ Chelsea
4. @ Southampton
5. v Brighton Hove Albion
6. @ Brentford

While West Ham United have played Newcastle United within their last 6 fixtures, the reverse is not true (it would be Newcastle's 7th fixture).

As I'm using the fixture IDs in the query to return the cumulative individual player stats the West Ham United players correctly return stats for their last 6 fixtures while the Newcastle United players incorrectly return stats for their last 7 fixtures.

This is the query I'm using to get the fixture IDs:

SELECT T1.ofm_feedId as teamfixtureID FROM (SELECT opta_fb_teams.ofbt_feedId, opta_fb_matches.ofm_feedId, ROW_NUMBER() OVER (PARTITION BY opta_fb_teams.ofbt_feedIdORDER BY opta_fb_matches.ofm_date DESC) as rownm FROM opta_fb_matches JOIN opta_fb_match_details ON opta_fb_matches.ofm_feedId = opta_fb_match_details.ofmd_matchId JOIN opta_fb_teams ON (opta_fb_teams.ofbt_feedId = opta_fb_matches.ofm_contestant1Id OR opta_fb_teams.ofbt_feedId = opta_fb_matches.ofm_contestant2Id) JOIN opta_fb_tournaments ON opta_fb_tournaments.oft_feedId=opta_fb_matches.ofm_competitionId JOIN opta_fb_stages ON opta_fb_stages.ofs_feedId=opta_fb_matches.ofm_stageId WHERE opta_fb_matches.ofm_competitionId = '2kwbbcootiqqgmrzs6o5inle5' AND opta_fb_matches.ofm_calendarId IN('8l3o9v8n8tva0bb2cds2dhatw') AND (opta_fb_match_details.ofmd_matchStatus = 'Played' OR opta_fb_match_details.ofmd_matchStatus = 'Playing') AND opta_fb_teams.ofbt_feedId IN('4txjdaqveermfryvbfrr4taf7','7vn2i2kd35zuetw6b38gw9jsz') AND CAST(CONCAT(opta_fb_matches.ofm_date, ' ', opta_fb_matches.ofm_time) AS DATETIME) <= '2022-04-06 10:47:48' AND opta_fb_stages.ofs_name NOT LIKE '%Preliminary%' ESCAPE '!' AND opta_fb_stages.ofs_name NOT LIKE '%Qualifying%' ESCAPE '!' ORDER BY opta_fb_teams.ofbt_feedId ASC, opta_fb_matches.ofm_date DESC) T1 WHERE rownm <= 6

I use PHP to put the returned fixture IDs into an array which I implode in the query below

Array([0] => eonyvfyzgwpo5478xbp6hb4t0[1] => enirby3iotkly2j661y6zsutw[2] => emnjv3vw4ao4al2cycn8dxrf8[3] => ekz3waoaqyf9ko78042joznkk[4] => ekk3msfet9cnb3nmpbts7p3pw[5] => ejd8d9qz0upvim4tzy05mil90[6] => eokbz0z3s5tmvg5vnf045k3ys[7] => ecagczapr68g0jnvwdvnz5e6s[8] => em1xnf3tebisn9vb5y5ac4844[9] => edsxdwrrp613xyosdgbdx47x0[10] => el6tuvaqro336qqzqkbhk52xg[11] => ejp4k1ni4uij4unn2p49705ck)

I then query the stats:

SELECT opta_fb_teams.ofbt_feedId, opta_fb_teams.ofbt_name, opta_fb_teams.ofbt_logo,  COUNT(DISTINCT(opta_fb_matches.ofm_feedId)) AS games,  (((SUM(IF(opta_fb_match_team_stats.ofmts_goals \> 0 AND opta_fb_match_team_stats.ofmts_goalsConceded \> 0, 1, 0))) / COUNT(DISTINCT(opta_fb_matches.ofm_feedId))) \* 100) AS btts,  (((SUM(IF(opta_fb_match_team_stats.ofmts_goals + opta_fb_match_team_stats.ofmts_goalsConceded \> 2, 1, 0))) / COUNT(DISTINCT(opta_fb_matches.ofm_feedId))) \* 100) AS over25,  AVG(opta_fb_match_team_stats.ofmts_possessionPercentage) AS possession,  SUM(opta_fb_match_team_stats.ofmts_goals) AS goals,  SUM(opta_fb_match_team_stats.ofmts_totalScoringAtt) AS shots,  SUM(opta_fb_match_team_stats.ofmts_totalYellowCard) AS yellow_cards,  SUM(opta_fb_match_team_stats.ofmts_totalRedCard) AS red_cards,  ((SUM(opta_fb_match_team_stats.ofmts_totalYellowCard)\*10)+(SUM(opta_fb_match_team_stats.ofmts_totalRedCard)\*25)) AS booking_points FROM opta_fb_matches  JOIN opta_fb_teams ON (opta_fb_matches.ofm_contestant1Id = opta_fb_teams.ofbt_feedId OR opta_fb_matches.ofm_contestant2Id = opta_fb_teams.ofbt_feedId) JOIN opta_fb_match_team_stats ON opta_fb_matches.ofm_feedId = opta_fb_match_team_stats.ofmts_matchId WHERE opta_fb_matches.ofm_feedId IN ('eonyvfyzgwpo5478xbp6hb4t0','enirby3iotkly2j661y6zsutw','emnjv3vw4ao4al2cycn8dxrf8','ekz3waoaqyf9ko78042joznkk','ekk3msfet9cnb3nmpbts7p3pw','ejd8d9qz0upvim4tzy05mil90','eokbz0z3s5tmvg5vnf045k3ys','ecagczapr68g0jnvwdvnz5e6s','em1xnf3tebisn9vb5y5ac4844','edsxdwrrp613xyosdgbdx47x0','el6tuvaqro336qqzqkbhk52xg','ejp4k1ni4uij4unn2p49705ck')  AND opta_fb_teams.ofbt_feedId IN ('4txjdaqveermfryvbfrr4taf7', '7vn2i2kd35zuetw6b38gw9jsz') GROUP BY opta_fb_teams.ofbt_feedId  ORDER BY opta_fb_teams.ofbt_name ASC 

Returned results:

Array
(
    [0] => Array
        (
            [ofbt_feedId] => 4txjdaqveermfryvbfrr4taf7
            [ofbt_name] => West Ham United
            [ofbt_logo] => west-ham-united-lfc.png
            [games] => 6
            [btts] => 66.6667
            [over25] => 50.0000
            [possession] => 44.583333
            [goals] => 7
            [shots] => 65
            [yellow_cards] => 8
            [red_cards] => 0
            [booking_points] => 80
        )

    [1] => Array
        (
            [ofbt_feedId] => 7vn2i2kd35zuetw6b38gw9jsz
            [ofbt_name] => Newcastle United
            [ofbt_logo] => newcastle-united.png
            [games] => 7
            [btts] => 57.1429
            [over25] => 42.8571
            [possession] => 43.971429
            [goals] => 8
            [shots] => 90
            [yellow_cards] => 13
            [red_cards] => 0
            [booking_points] => 130
        )

)

Which shows Newcastle having played 7 games rather than the required 6 and bases the stats calculations on 7 games rather than 6. How can I restrict the Newcastle stats in this example to be calculated from only the last 6 fixtures rather than the 7 shown here?

I hope I've explained this clearly.

MySQL Tables:

CREATE TABLE opta_fb_calendar (
  ofc_id int NOT NULL,
  ofc_feedId varchar(30) DEFAULT NULL,
  ofc_ocId int DEFAULT NULL,
  ofc_name varchar(50) DEFAULT NULL,
  ofc_startDate date DEFAULT NULL,
  ofc_endDate date DEFAULT NULL,
  ofc_active int DEFAULT NULL,
  ofc_lastUpdated date DEFAULT NULL,
  is_xscores int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_calendar (ofc_id, ofc_feedId, ofc_ocId, ofc_name, ofc_startDate, ofc_endDate, ofc_active, ofc_lastUpdated, is_xscores) VALUES
(1452, '8l3o9v8n8tva0bb2cds2dhatw', 20195, '2021/2022', '2021-08-13', '2022-05-22', 1, '2022-03-30', 0);

CREATE TABLE opta_fb_matches (
  ofm_id int NOT NULL,
  ofm_feedId varchar(30) NOT NULL,
  ofm_date date NOT NULL,
  ofm_time time NOT NULL,
  ofm_matchWeek int DEFAULT NULL,
  ofm_lastUpdated datetime NOT NULL,
  ofm_description varchar(200) NOT NULL,
  ofm_descriptionUrl varchar(200) NOT NULL,
  ofm_ruleset varchar(20) NOT NULL,
  ofm_competitionId varchar(30) NOT NULL,
  ofm_calendarId varchar(30) NOT NULL,
  ofm_stageId varchar(30) NOT NULL,
  ofm_seriesId varchar(30) DEFAULT NULL,
  ofm_venueId varchar(30) DEFAULT NULL,
  ofm_contestant1Id varchar(30) NOT NULL,
  ofm_contestant2Id varchar(30) NOT NULL,
  is_xscores int NOT NULL DEFAULT '0',
  ofm_tv_channel enum('amazon','bbc','bt','itv','sky') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  match_results_on_home_feed enum('No','Yes') NOT NULL,
  match_goals_on_home_feed enum('No','Yes') NOT NULL,
  is_streaming int DEFAULT NULL,
  team1_matchid_concat varchar(51) DEFAULT NULL,
  team2_matchid_concat varchar(51) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_matches (ofm_id, ofm_feedId, ofm_date, ofm_time, ofm_matchWeek, ofm_lastUpdated, ofm_description, ofm_descriptionUrl, ofm_ruleset, ofm_competitionId, ofm_calendarId, ofm_stageId, ofm_seriesId, ofm_venueId, ofm_contestant1Id, ofm_contestant2Id, is_xscores, ofm_tv_channel, match_results_on_home_feed, match_goals_on_home_feed, is_streaming, team1_matchid_concat, team2_matchid_concat) VALUES
(766801, 'eonyvfyzgwpo5478xbp6hb4t0', '2022-04-03', '14:00:00', 31, '2022-04-04 01:18:30', 'West Ham United vs Everton', 'west-ham-united-vs-everton', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, 'f3r2ll8qemj5r6ls4o4nsiok5', '4txjdaqveermfryvbfrr4taf7', 'ehd2iemqmschhj2ec0vayztzz', 0, NULL, 'No', 'No', NULL, '4txjdaqveermfryvbfrr4taf7_eonyvfyzgwpo5478xbp6hb4t0', 'ehd2iemqmschhj2ec0vayztzz_eonyvfyzgwpo5478xbp6hb4t0'),
(766802, 'eokbz0z3s5tmvg5vnf045k3ys', '2022-04-03', '16:30:00', 31, '2022-04-04 04:55:41', 'Tottenham Hotspur vs Newcastle United', 'tottenham-hotspur-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '9u92nb57uvffjjkbh6iakl0mi', '22doj4sgsocqpxw45h607udje', '7vn2i2kd35zuetw6b38gw9jsz', 0, NULL, 'No', 'No', NULL, '22doj4sgsocqpxw45h607udje_eokbz0z3s5tmvg5vnf045k3ys', '7vn2i2kd35zuetw6b38gw9jsz_eokbz0z3s5tmvg5vnf045k3ys'),
(766812, 'enirby3iotkly2j661y6zsutw', '2022-03-20', '16:30:00', 30, '2022-03-24 17:27:58', 'Tottenham Hotspur vs West Ham United', 'tottenham-hotspur-vs-west-ham-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '9u92nb57uvffjjkbh6iakl0mi', '22doj4sgsocqpxw45h607udje', '4txjdaqveermfryvbfrr4taf7', 0, NULL, 'No', 'No', NULL, '22doj4sgsocqpxw45h607udje_enirby3iotkly2j661y6zsutw', '4txjdaqveermfryvbfrr4taf7_enirby3iotkly2j661y6zsutw'),
(766820, 'emnjv3vw4ao4al2cycn8dxrf8', '2022-03-13', '14:00:00', 29, '2022-03-23 03:49:14', 'West Ham United vs Aston Villa', 'west-ham-united-vs-aston-villa', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, 'f3r2ll8qemj5r6ls4o4nsiok5', '4txjdaqveermfryvbfrr4taf7', 'b496gs285it6bheuikox6z9mj', 0, NULL, 'No', 'No', NULL, '4txjdaqveermfryvbfrr4taf7_emnjv3vw4ao4al2cycn8dxrf8', 'b496gs285it6bheuikox6z9mj_emnjv3vw4ao4al2cycn8dxrf8'),
(766826, 'em1xnf3tebisn9vb5y5ac4844', '2022-03-13', '14:00:00', 29, '2022-03-29 12:59:03', 'Chelsea vs Newcastle United', 'chelsea-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '3lnmxkrbtsvdffns96nqkggeg', '9q0arba2kbnywth8bkxlhgmdr', '7vn2i2kd35zuetw6b38gw9jsz', 0, NULL, 'No', 'No', NULL, '9q0arba2kbnywth8bkxlhgmdr_em1xnf3tebisn9vb5y5ac4844', '7vn2i2kd35zuetw6b38gw9jsz_em1xnf3tebisn9vb5y5ac4844'),
(766834, 'el6tuvaqro336qqzqkbhk52xg', '2022-03-05', '15:00:00', 28, '2022-03-30 00:00:40', 'Newcastle United vs Brighton & Hove Albion', 'newcastle-united-vs-brighton-&-hove-albion', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '8f6qv79cqqaeriyn46aftjyn0', '7vn2i2kd35zuetw6b38gw9jsz', 'e5p0ehyguld7egzhiedpdnc3w', 0, NULL, 'No', 'No', NULL, '7vn2i2kd35zuetw6b38gw9jsz_el6tuvaqro336qqzqkbhk52xg', 'e5p0ehyguld7egzhiedpdnc3w_el6tuvaqro336qqzqkbhk52xg'),
(766836, 'ekz3waoaqyf9ko78042joznkk', '2022-03-05', '17:30:00', 28, '2022-03-23 03:49:14', 'Liverpool vs West Ham United', 'liverpool-vs-west-ham-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '39tsgu0lj7rs44kxa9n7r6k4', 'c8h9bw1l82s06h77xxrelzhur', '4txjdaqveermfryvbfrr4taf7', 0, NULL, 'No', 'No', NULL, 'c8h9bw1l82s06h77xxrelzhur_ekz3waoaqyf9ko78042joznkk', '4txjdaqveermfryvbfrr4taf7_ekz3waoaqyf9ko78042joznkk'),
(766840, 'ekk3msfet9cnb3nmpbts7p3pw', '2022-02-27', '14:00:00', 27, '2022-03-24 22:55:41', 'West Ham United vs Wolverhampton Wanderers', 'west-ham-united-vs-wolverhampton-wanderers', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, 'f3r2ll8qemj5r6ls4o4nsiok5', '4txjdaqveermfryvbfrr4taf7', 'b9si1jn1lfxfund69e9ogcu2n', 0, NULL, 'No', 'No', NULL, '4txjdaqveermfryvbfrr4taf7_ekk3msfet9cnb3nmpbts7p3pw', 'b9si1jn1lfxfund69e9ogcu2n_ekk3msfet9cnb3nmpbts7p3pw'),
(766848, 'ejp4k1ni4uij4unn2p49705ck', '2022-02-26', '15:00:00', 27, '2022-03-29 13:06:51', 'Brentford vs Newcastle United', 'brentford-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '6rc5sj2ie03h51npt77nrf9qs', '7yx5dqhhphyvfisohikodajhv', '7vn2i2kd35zuetw6b38gw9jsz', 0, NULL, 'No', 'No', NULL, '7yx5dqhhphyvfisohikodajhv_ejp4k1ni4uij4unn2p49705ck', '7vn2i2kd35zuetw6b38gw9jsz_ejp4k1ni4uij4unn2p49705ck'),
(766851, 'ejd8d9qz0upvim4tzy05mil90', '2022-02-19', '12:30:00', 26, '2022-03-24 22:55:41', 'West Ham United vs Newcastle United', 'west-ham-united-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, 'f3r2ll8qemj5r6ls4o4nsiok5', '4txjdaqveermfryvbfrr4taf7', '7vn2i2kd35zuetw6b38gw9jsz', 0, NULL, 'No', 'No', NULL, '4txjdaqveermfryvbfrr4taf7_ejd8d9qz0upvim4tzy05mil90', '7vn2i2kd35zuetw6b38gw9jsz_ejd8d9qz0upvim4tzy05mil90'),
(766901, 'edsxdwrrp613xyosdgbdx47x0', '2022-03-10', '19:30:00', 21, '2022-03-17 13:53:05', 'Southampton vs Newcastle United', 'southampton-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '84m4magjm6fr4qwj9ak7ok279', 'd5ydtvt96bv7fq04yqm2w2632', '7vn2i2kd35zuetw6b38gw9jsz', 0, NULL, 'No', 'No', NULL, 'd5ydtvt96bv7fq04yqm2w2632_edsxdwrrp613xyosdgbdx47x0', '7vn2i2kd35zuetw6b38gw9jsz_edsxdwrrp613xyosdgbdx47x0'),
(766915, 'ecagczapr68g0jnvwdvnz5e6s', '2022-03-17', '19:45:00', 20, '2022-03-18 15:21:06', 'Everton vs Newcastle United', 'everton-vs-newcastle-united', 'Men', '2kwbbcootiqqgmrzs6o5inle5', '8l3o9v8n8tva0bb2cds2dhatw', '8letniaf2s1z04atatf62ok5w', NULL, '6wrxafh428mktq1p4gyxhh44c', 'ehd2iemqmschhj2ec0vayztzz', '7vn2i2kd35zuetw6b38gw9jsz', 0, 'amazon', 'No', 'No', NULL, 'ehd2iemqmschhj2ec0vayztzz_ecagczapr68g0jnvwdvnz5e6s', '7vn2i2kd35zuetw6b38gw9jsz_ecagczapr68g0jnvwdvnz5e6s');

CREATE TABLE opta_fb_match_details (
  ofmd_id int UNSIGNED NOT NULL,
  ofmd_homeContestantId varchar(30) NOT NULL,
  ofmd_awayContestantId varchar(30) NOT NULL,
  ofmd_periodId int DEFAULT NULL,
  ofmd_extra_time enum('No','Yes') DEFAULT 'No',
  ofmd_timestamp datetime DEFAULT NULL,
  ofmd_matchEndTimestamp datetime DEFAULT NULL,
  ofmd_matchStatus varchar(25) NOT NULL,
  ofmd_matchId varchar(30) NOT NULL,
  ofmd_winner varchar(20) NOT NULL,
  ofmd_relatedMatchId varchar(30) DEFAULT NULL,
  ofmd_aggregateWinnerId varchar(30) DEFAULT NULL,
  ofmd_matchLengthMin int DEFAULT NULL,
  ofmd_matchLengthSec int DEFAULT NULL,
  ofmd_scores_ht_home tinyint NOT NULL,
  ofmd_scores_ht_away tinyint NOT NULL,
  ofmd_scores_ft_home tinyint NOT NULL,
  ofmd_scores_ft_away tinyint NOT NULL,
  ofmd_scores_et_home tinyint NOT NULL,
  ofmd_scores_et_away tinyint NOT NULL,
  ofmd_scores_pen_home tinyint NOT NULL,
  ofmd_scores_pen_away tinyint NOT NULL,
  ofmd_scores_total_home tinyint NOT NULL,
  ofmd_scores_total_away tinyint NOT NULL,
  ofmd_scores_aggregate_home tinyint NOT NULL,
  ofmd_scores_aggregate_away tinyint NOT NULL,
  ofmd_attendance int NOT NULL,
  ofmd_weather varchar(50) NOT NULL,
  is_xscores int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_match_details (ofmd_id, ofmd_homeContestantId, ofmd_awayContestantId, ofmd_periodId, ofmd_extra_time, ofmd_timestamp, ofmd_matchEndTimestamp, ofmd_matchStatus, ofmd_matchId, ofmd_winner, ofmd_relatedMatchId, ofmd_aggregateWinnerId, ofmd_matchLengthMin, ofmd_matchLengthSec, ofmd_scores_ht_home, ofmd_scores_ht_away, ofmd_scores_ft_home, ofmd_scores_ft_away, ofmd_scores_et_home, ofmd_scores_et_away, ofmd_scores_pen_home, ofmd_scores_pen_away, ofmd_scores_total_home, ofmd_scores_total_away, ofmd_scores_aggregate_home, ofmd_scores_aggregate_away, ofmd_attendance, ofmd_weather, is_xscores) VALUES
(770251, 'ehd2iemqmschhj2ec0vayztzz', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-03-17 21:49:31', '2022-03-17 21:49:31', 'Played', 'ecagczapr68g0jnvwdvnz5e6s', 'home', NULL, NULL, 108, 25, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '', 0),
(773860, 'd5ydtvt96bv7fq04yqm2w2632', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-03-10 21:26:31', '2022-03-10 21:26:31', 'Played', 'edsxdwrrp613xyosdgbdx47x0', 'away', NULL, NULL, 100, 4, 1, 1, 1, 2, 0, 0, 0, 0, 1, 2, 0, 0, 0, '', 0),
(799447, '4txjdaqveermfryvbfrr4taf7', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-02-19 16:07:08', NULL, 'Played', 'ejd8d9qz0upvim4tzy05mil90', 'draw', NULL, NULL, 97, 32, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 0, 0, 0, '', 0),
(801136, '4txjdaqveermfryvbfrr4taf7', 'b9si1jn1lfxfund69e9ogcu2n', 14, 'No', '2022-02-27 15:55:47', '2022-02-27 15:55:47', 'Played', 'ekk3msfet9cnb3nmpbts7p3pw', 'home', NULL, NULL, 98, 23, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '', 0),
(801144, '7yx5dqhhphyvfisohikodajhv', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-02-26 16:56:05', '2022-02-26 16:56:05', 'Played', 'ejp4k1ni4uij4unn2p49705ck', 'away', NULL, NULL, 99, 19, 0, 2, 0, 2, 0, 0, 0, 0, 0, 2, 0, 0, 0, '', 0),
(802897, '7vn2i2kd35zuetw6b38gw9jsz', 'e5p0ehyguld7egzhiedpdnc3w', 14, 'No', '2022-03-05 16:54:05', '2022-03-05 16:54:05', 'Played', 'el6tuvaqro336qqzqkbhk52xg', 'home', NULL, NULL, 98, 10, 2, 0, 2, 1, 0, 0, 0, 0, 2, 1, 0, 0, 52214, '', 0),
(802899, 'c8h9bw1l82s06h77xxrelzhur', '4txjdaqveermfryvbfrr4taf7', 14, 'No', '2022-03-05 19:27:05', '2022-03-05 19:27:05', 'Played', 'ekz3waoaqyf9ko78042joznkk', 'home', NULL, NULL, 97, 12, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '', 0),
(803993, '9q0arba2kbnywth8bkxlhgmdr', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-03-13 15:52:17', '2022-03-13 15:52:17', 'Played', 'em1xnf3tebisn9vb5y5ac4844', 'home', NULL, NULL, 96, 20, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 40026, '', 0),
(804001, '4txjdaqveermfryvbfrr4taf7', 'b496gs285it6bheuikox6z9mj', 14, 'No', '2022-03-13 16:01:07', '2022-03-13 16:01:07', 'Played', 'emnjv3vw4ao4al2cycn8dxrf8', 'home', NULL, NULL, 103, 59, 0, 0, 2, 1, 0, 0, 0, 0, 2, 1, 0, 0, 0, '', 0),
(808686, '22doj4sgsocqpxw45h607udje', '4txjdaqveermfryvbfrr4taf7', 14, 'No', '2022-03-20 18:25:31', '2022-03-20 18:25:31', 'Played', 'enirby3iotkly2j661y6zsutw', 'home', NULL, NULL, 97, 43, 2, 1, 3, 1, 0, 0, 0, 0, 3, 1, 0, 0, 58685, '', 0),
(810847, '4txjdaqveermfryvbfrr4taf7', 'ehd2iemqmschhj2ec0vayztzz', 14, 'No', '2022-04-03 15:54:03', '2022-04-03 15:54:03', 'Played', 'eonyvfyzgwpo5478xbp6hb4t0', 'home', NULL, NULL, 96, 43, 1, 0, 2, 1, 0, 0, 0, 0, 2, 1, 0, 0, 59953, '', 0),
(810848, '22doj4sgsocqpxw45h607udje', '7vn2i2kd35zuetw6b38gw9jsz', 14, 'No', '2022-04-03 18:24:04', '2022-04-03 18:24:04', 'Played', 'eokbz0z3s5tmvg5vnf045k3ys', 'home', NULL, NULL, 97, 25, 1, 1, 5, 1, 0, 0, 0, 0, 5, 1, 0, 0, 57553, '', 0);

CREATE TABLE opta_fb_match_team_stats (
  ofmts_id int NOT NULL,
  ofmts_matchId varchar(30) NOT NULL,
  ofmts_teamId varchar(30) NOT NULL,
  ofmts_possessionPercentage decimal(10,2) NOT NULL,
  ofmts_fkFoulWon int NOT NULL,
  ofmts_totalYellowCard int NOT NULL,
  ofmts_totalScoringAtt int NOT NULL,
  ofmts_wonTackle int NOT NULL,
  ofmts_secondYellow int NOT NULL,
  ofmts_totalRedCard int NOT NULL,
  ofmts_penaltyWon int NOT NULL,
  ofmts_ownGoals int NOT NULL,
  ofmts_goalsConceded int NOT NULL,
  ofmts_goals int NOT NULL,
  ofmts_totalYellowCardOpp int NOT NULL,
  ofmts_totalRedCardOpp int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_match_team_stats (ofmts_id, ofmts_matchId, ofmts_teamId, ofmts_possessionPercentage, ofmts_fkFoulWon, ofmts_totalYellowCard, ofmts_totalScoringAtt, ofmts_wonTackle, ofmts_secondYellow, ofmts_totalRedCard, ofmts_penaltyWon, ofmts_ownGoals, ofmts_goalsConceded, ofmts_goals, ofmts_totalYellowCardOpp, ofmts_totalRedCardOpp) VALUES
(10782488, 'ejd8d9qz0upvim4tzy05mil90', '4txjdaqveermfryvbfrr4taf7', '47.00', 7, 3, 11, 8, 0, 0, 0, 0, 1, 1, 3, 0),
(10782489, 'ejd8d9qz0upvim4tzy05mil90', '7vn2i2kd35zuetw6b38gw9jsz', '53.00', 3, 3, 14, 7, 0, 0, 0, 0, 1, 1, 3, 0),
(11041190, 'ejp4k1ni4uij4unn2p49705ck', '7yx5dqhhphyvfisohikodajhv', '37.30', 6, 3, 6, 6, 0, 1, 0, 0, 2, 0, 1, 0),
(11041191, 'ejp4k1ni4uij4unn2p49705ck', '7vn2i2kd35zuetw6b38gw9jsz', '62.70', 8, 1, 26, 5, 0, 0, 0, 0, 0, 2, 3, 1),
(11117366, 'ekk3msfet9cnb3nmpbts7p3pw', '4txjdaqveermfryvbfrr4taf7', '39.00', 2, 1, 13, 9, 0, 0, 0, 0, 0, 1, 0, 0),
(11117367, 'ekk3msfet9cnb3nmpbts7p3pw', 'b9si1jn1lfxfund69e9ogcu2n', '61.00', 7, 0, 14, 9, 0, 0, 0, 0, 1, 0, 1, 0),
(11377494, 'el6tuvaqro336qqzqkbhk52xg', '7vn2i2kd35zuetw6b38gw9jsz', '32.40', 9, 1, 10, 8, 0, 0, 0, 0, 1, 2, 1, 0),
(11377496, 'el6tuvaqro336qqzqkbhk52xg', 'e5p0ehyguld7egzhiedpdnc3w', '67.60', 8, 1, 15, 6, 0, 0, 0, 0, 2, 1, 1, 0),
(11377498, 'ekz3waoaqyf9ko78042joznkk', 'c8h9bw1l82s06h77xxrelzhur', '68.60', 7, 2, 22, 6, 0, 0, 0, 0, 0, 1, 1, 0),
(11377499, 'ekz3waoaqyf9ko78042joznkk', '4txjdaqveermfryvbfrr4taf7', '31.40', 7, 1, 13, 8, 0, 0, 0, 0, 1, 0, 2, 0),
(11519348, 'edsxdwrrp613xyosdgbdx47x0', 'd5ydtvt96bv7fq04yqm2w2632', '68.10', 7, 1, 14, 5, 0, 0, 0, 0, 2, 1, 1, 0),
(11519349, 'edsxdwrrp613xyosdgbdx47x0', '7vn2i2kd35zuetw6b38gw9jsz', '31.90', 5, 1, 8, 8, 0, 0, 0, 0, 1, 2, 1, 0),
(11789236, 'emnjv3vw4ao4al2cycn8dxrf8', '4txjdaqveermfryvbfrr4taf7', '47.10', 3, 1, 11, 3, 0, 0, 0, 0, 1, 2, 0, 0),
(11789237, 'emnjv3vw4ao4al2cycn8dxrf8', 'b496gs285it6bheuikox6z9mj', '52.90', 8, 0, 13, 10, 0, 0, 0, 0, 2, 1, 1, 0),
(11789250, 'em1xnf3tebisn9vb5y5ac4844', '9q0arba2kbnywth8bkxlhgmdr', '72.80', 17, 4, 8, 8, 0, 0, 0, 0, 0, 1, 3, 0),
(11789252, 'em1xnf3tebisn9vb5y5ac4844', '7vn2i2kd35zuetw6b38gw9jsz', '27.20', 16, 3, 7, 6, 0, 0, 0, 0, 1, 0, 4, 0),
(11882282, 'ecagczapr68g0jnvwdvnz5e6s', 'ehd2iemqmschhj2ec0vayztzz', '38.60', 11, 2, 9, 9, 0, 1, 0, 0, 0, 1, 1, 0),
(11882283, 'ecagczapr68g0jnvwdvnz5e6s', '7vn2i2kd35zuetw6b38gw9jsz', '61.40', 12, 1, 17, 12, 0, 0, 0, 0, 1, 0, 2, 1),
(12155656, 'enirby3iotkly2j661y6zsutw', '22doj4sgsocqpxw45h607udje', '52.70', 7, 0, 17, 9, 0, 0, 0, 0, 1, 3, 1, 0),
(12155658, 'enirby3iotkly2j661y6zsutw', '4txjdaqveermfryvbfrr4taf7', '47.30', 7, 1, 6, 3, 0, 0, 0, 1, 3, 1, 0, 0),
(12721579, 'eonyvfyzgwpo5478xbp6hb4t0', '4txjdaqveermfryvbfrr4taf7', '55.70', 11, 1, 11, 5, 0, 0, 0, 0, 1, 2, 2, 1),
(12721581, 'eonyvfyzgwpo5478xbp6hb4t0', 'ehd2iemqmschhj2ec0vayztzz', '44.30', 9, 2, 13, 7, 1, 1, 0, 0, 2, 1, 1, 0),
(12721583, 'eokbz0z3s5tmvg5vnf045k3ys', '22doj4sgsocqpxw45h607udje', '60.80', 7, 2, 19, 10, 0, 0, 0, 0, 1, 5, 3, 0),
(12721585, 'eokbz0z3s5tmvg5vnf045k3ys', '7vn2i2kd35zuetw6b38gw9jsz', '39.20', 10, 3, 8, 9, 0, 0, 0, 0, 5, 1, 2, 0);

CREATE TABLE opta_fb_stages (
  ofs_id int NOT NULL,
  ofs_feedId varchar(30) NOT NULL,
  ofs_tournamentId varchar(30) NOT NULL,
  ofs_startDate date DEFAULT NULL,
  ofs_endDate date DEFAULT NULL,
  ofs_name varchar(50) NOT NULL,
  is_xscores int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_stages (ofs_id, ofs_feedId, ofs_tournamentId, ofs_startDate, ofs_endDate, ofs_name, is_xscores) VALUES
(12080, '8letniaf2s1z04atatf62ok5w', '2kwbbcootiqqgmrzs6o5inle5', '2021-08-13', '2022-05-22', 'Regular Season', 0);

CREATE TABLE opta_fb_teams (
  ofbt_id int NOT NULL,
  ofbt_feedId varchar(30) NOT NULL,
  ofbt_name varchar(30) NOT NULL,
  ofbt_code varchar(5) DEFAULT NULL,
  ofbt_nameUrl varchar(30) NOT NULL,
  ofbt_countryId varchar(30) DEFAULT NULL,
  ofbt_logo varchar(100) DEFAULT NULL,
  is_xscores int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_teams (ofbt_id, ofbt_feedId, ofbt_name, ofbt_code, ofbt_nameUrl, ofbt_countryId, ofbt_logo, is_xscores) VALUES
(22589, '4txjdaqveermfryvbfrr4taf7', 'West Ham United', 'WHU', 'west-ham-united', '1fk5l4hkqk12i7zske6mcqju6', 'west-ham-united-lfc.png', 0),
(22609, '7vn2i2kd35zuetw6b38gw9jsz', 'Newcastle United', 'NEW', 'newcastle-united', '1fk5l4hkqk12i7zske6mcqju6', 'newcastle-united.png', 0);

CREATE TABLE opta_fb_tournaments (
  oft_id int NOT NULL,
  oft_feedId varchar(30) NOT NULL,
  oft_ocId int DEFAULT NULL,
  oft_opId int DEFAULT NULL,
  oft_parentId int DEFAULT NULL,
  oft_name varchar(60) DEFAULT NULL,
  oft_nameUrl varchar(60) NOT NULL,
  oft_countryId varchar(30) DEFAULT NULL,
  oft_isFriendly int DEFAULT NULL,
  oft_competitionFormat varchar(50) DEFAULT NULL,
  oft_type varchar(20) DEFAULT NULL,
  is_xscores int NOT NULL,
  oft_order int NOT NULL DEFAULT '999',
  oft_display int NOT NULL DEFAULT '0',
  oft_logo varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO opta_fb_tournaments (oft_id, oft_feedId, oft_ocId, oft_opId, oft_parentId, oft_name, oft_nameUrl, oft_countryId, oft_isFriendly, oft_competitionFormat, oft_type, is_xscores, oft_order, oft_display, oft_logo) VALUES
(28161, '2kwbbcootiqqgmrzs6o5inle5', 8, 8, NULL, 'Premier League', 'premier-league', '1fk5l4hkqk12i7zske6mcqju6', 0, 'Domestic league', 'men', 0, 4, 1, 'epl.png');


ALTER TABLE opta_fb_calendar
  ADD PRIMARY KEY (ofc_id),
  ADD UNIQUE KEY ofc_feedId_2 (ofc_feedId),
  ADD KEY ofc_feedId (ofc_feedId),
  ADD KEY ofc_name (ofc_name);

ALTER TABLE opta_fb_matches
  ADD PRIMARY KEY (ofm_id),
  ADD UNIQUE KEY ofm_feedId_2 (ofm_feedId),
  ADD KEY ofm_feedId (ofm_feedId),
  ADD KEY ofm_competitionId (ofm_competitionId),
  ADD KEY ofm_calendarId (ofm_calendarId),
  ADD KEY ofm_stageId (ofm_stageId),
  ADD KEY ofm_venueId (ofm_venueId),
  ADD KEY ofm_contestant1Id (ofm_contestant1Id),
  ADD KEY ofm_contestant2Id (ofm_contestant2Id),
  ADD KEY ofm_date (ofm_date),
  ADD KEY ofm_descriptionUrl (ofm_descriptionUrl),
  ADD KEY ofm_time (ofm_time),
  ADD KEY is_xscores (is_xscores),
  ADD KEY CompIdCont1Cont2Date (ofm_competitionId,ofm_contestant1Id,ofm_contestant2Id,ofm_date),
  ADD KEY ConcatTeamsMatchId (team1_matchid_concat,team2_matchid_concat);
ALTER TABLE opta_fb_matches ADD FULLTEXT KEY DescriptionSearch (ofm_description);

ALTER TABLE opta_fb_match_details
  ADD PRIMARY KEY (ofmd_id),
  ADD UNIQUE KEY ofmd_matchId_2 (ofmd_matchId),
  ADD KEY ofmd_homeContestantId (ofmd_homeContestantId),
  ADD KEY ofmd_awayContestantId (ofmd_awayContestantId),
  ADD KEY ofmd_periodId (ofmd_periodId),
  ADD KEY ofmd_matchId (ofmd_matchId),
  ADD KEY ofmd_relatedMatchId (ofmd_relatedMatchId),
  ADD KEY ofmd_aggregateWinnerId (ofmd_aggregateWinnerId),
  ADD KEY ofmd_matchStatus (ofmd_matchStatus);

ALTER TABLE opta_fb_match_team_stats
  ADD PRIMARY KEY (ofmts_id),
  ADD KEY ofmts_matchId (ofmts_matchId),
  ADD KEY ofmts_teamId (ofmts_teamId);

ALTER TABLE opta_fb_stages
  ADD PRIMARY KEY (ofs_id),
  ADD UNIQUE KEY ofs_feedId_2 (ofs_feedId),
  ADD KEY ofs_feedId (ofs_feedId),
  ADD KEY ofs_name (ofs_name);

ALTER TABLE opta_fb_teams
  ADD PRIMARY KEY (ofbt_id),
  ADD UNIQUE KEY ofbt_feedId_2 (ofbt_feedId),
  ADD KEY ofbt_feedId (ofbt_feedId),
  ADD KEY ofbt_countryId (ofbt_countryId);
ALTER TABLE opta_fb_teams ADD FULLTEXT KEY NameSearch (ofbt_name);

ALTER TABLE opta_fb_tournaments
  ADD PRIMARY KEY (oft_id),
  ADD UNIQUE KEY oft_feedId_2 (oft_feedId),
  ADD KEY oft_feedId (oft_feedId),
  ADD KEY oft_ocId (oft_ocId),
  ADD KEY oft_opId (oft_opId),
  ADD KEY oft_countryId (oft_countryId),
  ADD KEY oft_nameUrl (oft_nameUrl),
  ADD KEY oft_feedId_3 (oft_feedId),
  ADD KEY IdAndName (oft_feedId,oft_nameUrl),
  ADD KEY countryIdFeedIdTourName (oft_countryId,oft_feedId,oft_name);

db<>fiddle here