SQLite 3.39

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

create table matches (
  match_id integer,
  uts integer,
  player_id integer,
  opponent_id integer
);

✓
insert into matches values
(39943359,    1678449600,   1,    2), 
(39943361,    1678449600,   3,    4),   
(39943363,    1678451400,   5,    6),     
(39943365,    1678451400,   5,    6),  
(39943367,    1678453200,   5,    6),
(39943369,    1678453200,   1,    3),
(39943371,    1678455000,   1,    2),  
(39943373,    1678455000,   1,    5);
✓
with cte as (
  select *, row_number() over (partition by player_id, opponent_id order by uts) as rn
  from matches
)
select match_id, uts, player_id, opponent_id, sum(1) over (partition by player_id, opponent_id order by rn) - 1 as cnt
from cte
order by match_id


match_id
uts
player_id
opponent_id
cnt


39943359
1678449600
1
2
0

39943361
1678449600
3
4
0

39943363
1678451400
5
6
0

39943365
1678451400
5
6
1

39943367
1678453200
5
6
2

39943369
1678453200
1
3
0

39943371
1678455000
1
2
1

39943373
1678455000
1
5
0

match_id	uts	player_id	opponent_id	cnt
39943359	1678449600	1	2	0
39943361	1678449600	3	4	0
39943363	1678451400	5	6	0
39943365	1678451400	5	6	1
39943367	1678453200	5	6	2
39943369	1678453200	1	3	0
39943371	1678455000	1	2	1
39943373	1678455000	1	5	0