Need a SQL Server query to eliminate the Highlighted Rows ( Returning Routes in Flight)

I have a requirement where I need to eliminate all the rows which have returning flight routes.

I have highlighted the routes in the orange color which depicts this behavior.

For example, row 1 has one route BKI - MYY and row 4 has MYY - BKI. I need a flag (Boolean 1/0) that checks the entire row and sees if it’s a returning flight or not.

Note: there are a few scenarios where the flight started at 10:00 PM night today and returned the next day early morning. if it is a consecutive day then we need to eliminate that rows as well.

Any thought on how to solve this?

Gsheet link : Sample Data : https://docs.google.com/spreadsheets/d/1ibrUfOTMEx4i-erDQnIfszdcEvJKU_oFcnMBFcGbvyM/edit?usp=sharing

Definition: Return flight is a two-way journey by air. If a flight is starting from the source and after certain hop/route returns backs to the same source (Departure Station)

std_utc = Departure Time Sta_utc = Arival Time

with AllFlight as
(
select row_number()over(order by std_utc)rn, aoc,hub,flight ,departure,arrival,std_utc,sta_utc
from TABLENAME01
),
returnFlight as 
(
select * from AllFlight s
where exists 
(
    select * from AllFlight s2 
    where s2.rn<s.rn and s2.arrival=s.departure and s2.departure=s.arrival 
    and 
    (date_diff(EXTRACT(DATE FROM s.sta_utc),EXTRACT(DATE FROM s2.std_utc), DAY))<=1
)
)

select aoc,hub,flight ,departure ,arrival,std_utc  ,sta_utc from AllFlight where rn not in (select rn from returnFlight)

This query is kept on running and not giving me the desired output. Note: I am using Bigquery

Answer

I have created a table named S65828793 with your provided data. First I have numbered the rows in ascending sequence of departure time. Then from that I have identified the flights that have been another flight within two consecutive days from opposite direction and marked those as returning flight. Then at last I have excluded all the returning flight from the total flight list.

with AllFlight as (
select row_number()over(order by departuretime)rn, aoc  ,hub    ,flight ,departure  ,arrival    ,DepartureTime  ,ArrivalTime from S65828793  
),
returnFlight as (select * from AllFlight s
where exists (select * from AllFlight s2 where s2.rn<s.rn and s2.arrival=s.departure and s2.departure=s.arrival and datediff(day,cast(s.ArrivalTime as date),cast(s2.departuretime as date))<=1))
select aoc  ,hub    ,flight ,departure  ,arrival    ,DepartureTime  ,ArrivalTime from AllFlight where rn not in (select rn from returnFlight)

Result: