Solving SQL Interview Query using a 'VERY IMPORTANT SQL concept'

In this video, we look at another SQL interview query. We shall solve this problem using "VERY IMPORTANT SQL concept".
The problem statement might seem complex but the solution is pretty simple. I will explain how the problem can be solved, the logic and thinking behind solving such SQL problems during interviews.

Download the dataset and SQL queries used in this video for free from my blog. Link below:

Timestamp:
00:00 Intro
00:29 Understanding the problem statement
01:53 Coming up with the logic to solve the problem
06:34 Solution to SQL Problem

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

THANK YOU,
Thoufiq

[Anonymous-lezr]

I think we can simply write the query using least, Greatest funtions (in Oracle Database)

SELECT DISTINCT
least(SOURCE, DESTINATION) SOURCE,
greatest(SOURCE, DESTINATION) DESTINATION,

DISTANCE
FROM table1;

It will give the desired results

[wecusyi]

I think the below one also works.
with cte as
(select *,
row_number() over(partition by buckets) as rn
from(
select *,
ntile(3) over(partition by distance) as buckets
from src_dest_distance))

select source, destination, distance
from cte
where rn<2;

[MaVinkal]

I guess this can be also one more approach with greatest and least function:

select distinct greatest(source, destination) as source, least(source, destination) as destination, distance from src_dest_distance;

[George-izce]

A self anti semi join sounds easier to me:

select *
from src_dest_distance a
where not exists
(select 1 from src_dest_distance a where a.source=b.destination and a.destination=b.source and b.source<b.destination)

[siddarthkollipara]

Actually @techTFQ it would be better if you add t1.dest = t2.source bcz in the sample data we don't have repetition of source but that is mostly possible to uniquely identify we could add this condition

[theamigo]

It's great to see tutorials like this posted. However, it should be noted that the solution presented only works for a very specific dataset. Two potential failure cases come to mind:
1) Adding the city pair Bangalore <-> Chennai will fail. As others have noted, the fix for this is to make the where clause check both T1.Source = T2.Dest and T1.Dest = T2.Source
2) It assumes that every city pair shows in both orders. If a city pair is only listed in one direction, it won't match on the self join.

Others have mentioned using least() and greatest(). Not just an alternative, but using such functions solves both of the problems noted above:

SELECT DISTINCT GREATEST(source, destination) AS source, LEAST(source, destination) AS destination, distance FROM cities;

Note: tested with MariaDB, other engines may vary.

[whatawonderfulworld]

Easier:

select
source as destination,
destination as source,
distance
from input
union
select
destination,
source,
distance
from input

Explanation: union will automatically remove duplicate rows ;)

[dedeegal]

I think my solution is much simpler and therefore more understandable:

select source c1, dest c2, dist from input where source<=dest
union
select dest c1, source c2, dist from input where source>dest

[extraincomesuz]

I just learned JOIN today in SQL and this was a great lesson to add to my earlier lesson. I also liked the addition of the ID column. Thank you!

[KisaanTuber]

Again an awesome video with great explanation. Just one suggestion to the join condition. Should we include T1.source = T2.destination and T1.destination = T2.source and T.id < T2.id so that we match only the required rows. The condition T1.source = T2.destination and T.id < T2.id may join non required rows as well which is not present in your data example. Like Bangalore -> Hyderabad and Chennai -> Bangalore. Let me know your thoughts. Thanks

[Dangerousdaze]

This might sound daft, but given the problem/solution set out at 2:20 why not just SELECT * FROM INPUT WHERE SOURCE IN ('Bangalore', 'Mumbai', 'Chennai');

[sub-harmonik]

For this data set it's fine but it would be cool to incorporate the possibility of the same city showing up in a different pair.
I guess you could just also add the condition that t2.source = t1.destination

[sahilummat]

Hey

My solution for the same is
;with cte as (
select *,
case when source <destination then source else destination end as source1
, case when source >destination then source else destination end as destination1
from src_dest_distance)

select source1, destination1, max(distance)

from cte
group by source1, destination1

[ganeshv]

we can solve it through not exists operator also right.

with cte as
(select *, row_number() over as id from src_dest_distance)
select * from cte AS t1 where not exists (select * from cte AS t2 where t1.source = t2.destination and t1.destination = t2.source and t1.id>t2.id);

[parveen]

approach for specific dataset
select * from src_dest_distance
where source<destination
these two line will suffice and give desired output

[yptjces]

select distinct s, d, distance from
(select *, case when source >destination then source else destination end s,
case when source < destination then source else destination end d
from src_dest_distance)x

[excelanilkumar]

IT IS NOT WORKING IN ORACLE WITH OUT OVER() CLAUSE

[shashishekhar----]

This has to be one of the best IT channels out there.

[protapnandi]

Great solution, if the source and destination are not strict, we can do simple trick-

select * from src_dest_distance where source<destination;

[devarajanmurugesan]

Looking at the output, it is a alternative row of the input table. In this instance, I believe we can divide the row number by 2. Where ever the value is not equal to zero, the result is the output table.

Select *
From (Select *, row_number() over () as row
from input_table) t1
where row % 2 <> 0