Postgres 14

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

CREATE TABLE tb_athlete    (
  athlete_id    CHARACTER(7) NOT NULL,
  name          CHARACTER VARYING(50) NOT NULL,
  country       CHARACTER VARYING(3) NOT NULL,
  substitute_id  CHARACTER(7) DEFAULT NULL,
  CONSTRAINT pk_athlete PRIMARY KEY (athlete_id),
  CONSTRAINT fk_athlete_substitute FOREIGN KEY (substitute_id) REFERENCES tb_athlete (athlete_id)
);

INSERT INTO tb_athlete(athlete_id, name, country, substitute_id) VALUES('1349038','AALERUD Katrine','NOR',NULL);
INSERT INTO tb_athlete(athlete_id, name, country, substitute_id) VALUES('1393212','ABASS Abobakr','SUD',NULL);
INSERT INTO tb_athlete(athlete_id, name, country, substitute_id) VALUES('1318621','ABDALLA Abubaker Haydar','QAT',NULL);
INSERT INTO tb_athlete(athlete_id, name, country, substitute_id) VALUES('1451030','ABDEL LATIF Radwa','EGY',NULL);

CREATE TABLE tb_discipline  (
  discipline_id  INT NOT NULL,
  name           CHARACTER VARYING(50) NOT NULL,
  inventor       CHARACTER VARYING(50) NOT NULL,
  type           CHARACTER VARYING(10) NOT NULL,
  object_type    CHARACTER VARYING(20) DEFAULT NULL, 
  CONSTRAINT ck_discipline_type CHECK (type IN ('RUN', 'JUMP', 'THROW')),
  CONSTRAINT pk_discipline PRIMARY KEY (discipline_id)
);

INSERT INTO tb_discipline(discipline_id, name, inventor, type, object_type) VALUES(16,'Triathlon','Carina Melina','RUN',null);

CREATE TABLE tb_register (
  athlete_id    CHARACTER(7) NOT NULL,
  round_number  INT NOT NULL,
  discipline_id INT NOT NULL,
  register_position INT DEFAULT NULL,
  CONSTRAINT pk_register PRIMARY KEY (athlete_id, round_number, discipline_id),
  CONSTRAINT fk_register_athlete FOREIGN KEY (athlete_id) REFERENCES tb_athlete (athlete_id)
--  CONSTRAINT fk_register_round FOREIGN KEY (discipline_id, round_number) REFERENCES tb_round (discipline_id, round_number)
);

INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1349038', 3, 16, 0);
INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1393212', 3, 16, 1);
INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1318621', 3, 16, 2);
INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1451030', 3, 16, 3);
--INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1343607', 3, 16, 4);
--INSERT INTO tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1435826', 3, 16, 5);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

WITH RECURSIVE athlete_hierarchy (discipline_id, discipline_name, round_number, register_position, a_position) AS
(SELECT 
    d.discipline_id,
    d.name,
    r.round_number,
    r.register_position,
    r.register_position || ':' || CAST (a.name AS TEXT)
  FROM tb_register AS r
  JOIN tb_discipline AS d
    ON r.discipline_id = d.discipline_id
  JOIN tb_athlete AS a
    ON r.athlete_id = a.athlete_id
  WHERE r.round_number = 3
    AND r.register_position = 0
  UNION ALL
  SELECT
    ah.discipline_id,
    ah.discipline_name,
    ah.round_number,
    r.register_position,
    ah.a_position || ' -> ' || r.register_position || ':' || CAST (a.name AS TEXT)
  FROM tb_register AS r
 INNER JOIN athlete_hierarchy AS ah
    ON ah.discipline_id = r.discipline_id
   AND ah.round_number = r.round_number
   AND ah.register_position = r.register_position - 1
 INNER JOIN tb_athlete AS a
    ON r.athlete_id = a.athlete_id
)
SELECT discipline_name, round_number, register_position, a_position
FROM athlete_hierarchy
ORDER BY register_position

discipline_name	round_number	register_position	a_position
Triathlon	3	0	0:AALERUD Katrine
Triathlon	3	1	0:AALERUD Katrine -> 1:ABASS Abobakr
Triathlon	3	2	0:AALERUD Katrine -> 1:ABASS Abobakr -> 2:ABDALLA Abubaker Haydar
Triathlon	3	3	0:AALERUD Katrine -> 1:ABASS Abobakr -> 2:ABDALLA Abubaker Haydar -> 3:ABDEL LATIF Radwa

SELECT 4