MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `game` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `tournament_id` int NOT NULL,
  `round_id` tinyint NOT NULL,
  `p1_id` int NOT NULL,
  `p2_id` int NOT NULL,
  `p1_stat` int DEFAULT NULL,
  `p2_stat` int DEFAULT NULL,
  PRIMARY KEY (`id_`));
INSERT INTO `game` VALUES (1,NULL,1,4,1,3,2,3),(2,NULL,1,5,1,4,4,6),(3,NULL,1,9,1,5,6,9),(4,NULL,1,10,2,1,8,12),(5,NULL,2,4,1,2,10,15),(6,NULL,2,5,4,1,12,18);
CREATE TABLE `tournament` (
  `id_` int NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `rank_id` tinyint NOT NULL,
  `surf_id` tinyint NOT NULL,
  PRIMARY KEY (`id_`))
INSERT INTO `tournament` VALUES (1,'1997-01-01 00:00:00',2,1),(2,'1997-01-06 00:00:00',2,1)
WITH union_matches AS (
	SELECT 
		g.id_ AS match_id,
		t.date_time AS tournament_date,
		g.round_id AS round_id,
        1 AS player_num,
		g.p1_id AS player_id,
		g.p2_id AS opponent_id,
		g.p1_stat AS player_stat,
		g.p2_stat AS opponent_stat
	FROM
		game AS g
			JOIN
		tournament AS t ON t.id_ = g.tournament_id 
	UNION SELECT 
		g.id_ AS match_id,
		t.date_time AS tournament_date,
		g.round_id AS round_id,
        2 AS player_num,
		g.p2_id AS player_id,
		g.p1_id AS opponent_id,
		g.p2_stat AS player_stat,
		g.p1_stat AS opponent_stat
	FROM
		game AS g
			JOIN
		tournament AS t ON t.id_ = g.tournament_id
)
SELECT 
    match_id,
    player_num,
    player_id,
    opponent_id,
    player_stat,
    opponent_stat,
    LAG(player_stat, 1) OVER (PARTITION BY player_id ORDER BY tournament_date, round_id) AS wrong_player_prev_stat,
    LAG(opponent_stat, 1) OVER (PARTITION BY opponent_id ORDER BY tournament_date, round_id) AS wrong_opponent_prev_stat
FROM
    union_matches
ORDER BY
	tournament_date, round_id, player_num


match_id
player_num
player_id
opponent_id
player_stat
opponent_stat
wrong_player_prev_stat
wrong_opponent_prev_stat


1
1
1
3
2
3
null
null

1
2
3
1
3
2
null
null

2
1
1
4
4
6
2
null

2
2
4
1
6
4
null
2

3
1
1
5
6
9
4
null

3
2
5
1
9
6
null
4

4
1
2
1
8
12
null
6

4
2
1
2
12
8
6
null

5
1
1
2
10
15
12
8

5
2
2
1
15
10
8
12

6
1
4
1
12
18
6
10

6
2
1
4
18
12
10
6

match_id	player_num	player_id	opponent_id	player_stat	opponent_stat	wrong_player_prev_stat	wrong_opponent_prev_stat
1	1	1	3	2	3	null	null
1	2	3	1	3	2	null	null
2	1	1	4	4	6	2	null
2	2	4	1	6	4	null	2
3	1	1	5	6	9	4	null
3	2	5	1	9	6	null	4
4	1	2	1	8	12	null	6
4	2	1	2	12	8	6	null
5	1	1	2	10	15	12	8
5	2	2	1	15	10	8	12
6	1	4	1	12	18	6	10
6	2	1	4	18	12	10	6