MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.3.31-MariaDB


create table my_table (foo int, user_id_1 int, user_id_2 int);
insert into my_table values (42,53,687);
insert into my_table values (42,687,53);
insert into my_table values (40,53,9832);
insert into my_table values (40,9832,53);
insert into my_table values (49,53,12);
insert into my_table values (39,53,9832);
insert into my_table values (39,9832,53);
select * from my_table;


foo
user_id_1
user_id_2


42
53
687

42
687
53

40
53
9832

40
9832
53

49
53
12

39
53
9832

39
9832
53


SELECT
    us.foo,
    us.user_id_1,
    us.user_id_2,
    (
        SELECT COUNT(*)
        FROM my_table x
        WHERE x.foo >= us.foo
            AND x.user_id_2 = us.user_id_1
    ) AS position
FROM my_table us
ORDER BY user_id_1, position;


foo
user_id_1
user_id_2
position


49
53
12
0

42
53
687
1

40
53
9832
2

39
53
9832
3

42
687
53
1

40
9832
53
1

39
9832
53
2