sql | apple interview question and answer | sql scenario based interview questions and answers #sql

Problem Statement :

Create table code :
=================

create table flight (
source varchar(10),
destination varchar(10)
);

insert into flight(source, destination) values('A','B'),('C','D'),('B','A'),('D','C');

#apple #interview #dataengineers

[nupoornawathey]

This problem is similar to Thoufiq's 1st problem from 30 days sql playlist, redundant pairs. I think you also solved the same problem in PySpark. It can be solved with same approach jic if in build methods cannot be used.

with cte as (
select
*,
case when source > destination then concat(source, destination)
when source < destination then concat(destination, source)
end as grp
from flight
), cte2 as (
select
*,
row_number() over(partition by grp order by source, destination) rn
from cte
)
select source, destination from cte2 where rn=1 ;

[Np-dtfy]

great content.following from last 3 weeks and content is really amazing.thanks brother

[ayanghosh]

please share the method using self join. also the union. humble request.

[kalaivanik]

select a.source, a.destination from flight a join flight b on a.source < b.source and a.destination = b.source;