Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE travels
(
Day DATE,
Flight_Code VARCHAR2(7),
Airplane_Code VARCHAR2(7),
PRIMARY KEY(day,flight_code)
);
CREATE TABLE boardings
(
Passport VARCHAR2(7),
Day DATE,
Flight_Code VARCHAR2(7),
Luggage_Weight NUMBER(5,2),
PRIMARY KEY(passport,day,flight_code)
);
INSERT INTO travels VALUES (DATE '2020-04-04', 'RY1234', 'ABCD');
1 rows affected

INSERT INTO travels VALUES (DATE '2020-04-03', 'RY1234', 'ABCD');
1 rows affected

INSERT INTO boardings VALUES ('ES1234', DATE '2020-04-04', 'RY1234', '51');
1 rows affected

INSERT INTO boardings VALUES ('ES5678', DATE '2020-04-04', 'RY1234', '50');
1 rows affected

INSERT INTO boardings VALUES ('ES9101', DATE '2020-04-03', 'RY1234', '100');
1 rows affected

SELECT t.airplane_code,
       t.day,
       t.flight_code
       FROM travels t
            INNER JOIN (SELECT b.day,
                               b.flight_code,
                               dense_rank() OVER (ORDER BY sum(b.luggage_weight) DESC) dr
                               FROM boardings b
                               GROUP BY b.day,
                                        b.flight_code) x
                       ON x.day = t.day
                          AND x.flight_code = t.flight_code
            WHERE x.dr = 1;


AIRPLANE_CODE
DAY
FLIGHT_CODE


ABCD
04-APR-20
RY1234