https://www.youtube.com/watch?v=rGG6FAYHDNc&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=24

-- Create tickets table
CREATE TABLE tickets (
    airline_number VARCHAR(10),
    origin VARCHAR(3),
    destination VARCHAR(3),
    oneway_round CHAR(1),
    ticket_count INT
);

-- Insert data into tickets table
INSERT INTO tickets (airline_number, origin, destination, oneway_round, ticket_count)
VALUES
    ('DEF456', 'BOM', 'DEL', 'O', 150),
    ('GHI789', 'DEL', 'BOM', 'R', 50),
    ('JKL012', 'BOM', 'DEL', 'R', 75),
    ('MNO345', 'DEL', 'NYC', 'O', 200),
    ('PQR678', 'NYC', 'DEL', 'O', 180),
    ('STU901', 'NYC', 'DEL', 'R', 60),
    ('ABC123', 'DEL', 'BOM', 'O', 100),
    ('VWX234', 'DEL', 'NYC', 'R', 90);

WITH
UNION_UNION ALL

WITH CTE AS(
SELECT origin,destination,ticket_count FROM tickets
UNION ALL
SELECT destination,origin,ticket_count FROM tickets
WHERE oneway_round = 'R') 
SELECT origin,destination,SUM(ticket_count) AS cnt_ticket FROM CTE
GROUP BY origin,destination
ORDER BY cnt_ticket DESC
LIMIT 1
;

SUBQUERY

SELECT origin,destination,SUM(ticket_count) AS tc 
FROM (SELECT origin,destination,ticket_count FROM tickets
UNION ALL
SELECT destination,origin,ticket_count FROM tickets
WHERE oneway_round = 'R') abc
GROUP BY origin,destination
ORDER BY tc DESC
LIMIT 1;