Tricky SQL Interview Query | Practice Solving SQL Interview Queries

[@gokberkoksuzoglu7920]

You can try this,
with a as (
SELECT
case when ASCII(source)

[@sahilummat8555]

Hey Thoufiq
loved your content.
A more simple solution from my end.
with cte as (
select *
,case when srcdest then src else dest end as dest1
from src_dest_dist_2)
select src1,dest1,cast(avg(distance) as decimal(10,2)) as avgdist
from cte
group by src1,dest1

[@abhinavkumar-jj2dx]

your solution is much easier to understand;

[@Sesquipedaliofobia]

perfect

[@ofirblum]

I've got this:
with cte as (
select least(src, dest) as src, greatest(src, dest) as dest,
distance
from src_dest_distance
)
select src, dest, avg(distance) as avg_distance
from cte
group by src, dest
order by src

[@vikashagarwal4305]

I think you should include the condition t1.dest = t2.src to get the general solution in case if we have distances like A to K and K to A in the original table

[@avi8016]

I didn't get this can you please explain?

[@vikashagarwal4305]

@@avi8016 for a particular source if we have different destinations then toufiq's solution would fail

[@sandeepagarwal5627]

@@vikashagarwal4305 very true, need to work Toufiq upon some different and easy solutions

[@shubhamagrawal7068]

Approach discussed in the video is complex. Here is the simplest approach (MySQL) : -
SELECT
IF(src < dest, src, dest) AS src,
IF(dest > src, dest, src) AS dest,
ROUND(AVG(distance), 2) AS avg_dist
FROM src_dest_dist
GROUP BY 1,2

[@hardas81]

Same results with a slightly different approach:
with cte_1 as
(select *,
case when src dst then dst||src
end as path
from src_dest_dist),
cte_2 as
(select *,
round(avg(distance) over (partition by path)::numeric, 2) as avg_distance,
row_number() over (partition by path) from cte_1)
select src, dst, avg_distance
from cte_2
where row_number = 1;

[@VinkalGhodasra]

with
table1 as (
select greatest(src, dest) as src, least(src, dest) as dest, distance
from src_dest_dist
)
select src, dest, avg(distance) as avg_distance from table1 group by src, dest
similar solution I had also commented in the previous video.

[@davode76166]

Much easier

[@EldhoseJoseph]

🙄 why ur taking average from greatest and least? Isn't that range? There r three values for AB.
Seems ur code is wrong.

[@fathimafarahna2633]

You are simply a gem

[@vivekmahadevan9838]

with t1 as
(select *,
case when src>dest then dest else src end as d1,
case when src>dest then src else dest end as d2
from src_dest_distance_2)
select d1,d2,round(avg(distance)::numeric,2)
from t1
group by d1,d2
order by d1,d2

[@mahendrachowdarysannumuri2657]

very simple and is working for source and destination which doesn't have the vice versa route. Thanks for the solution bro

[@weiwang219]

The solution provided by Vivek Mahadevan is a better choice cause there are some scenarios when the solution in this video will not work.

[@shekhark1139]

Your explanation is good. Thank you very much.. Do more videos, you have a great command on this topics.

[@rupamkarmakar2315]

Hey Thoufiq, thank you for the amazing SQL tutorial videos. Can you make a separate video on date & time manipulation. Can't seem to find any such videos in youtube which shows all the date and time manipulations.

[@venkatareddykunduru1828]

easy way:
select src,dest,round(ag,2) from
(select *,avg(distance) over(partition by grp) as ag,row_number() over(partition by grp) as rn from
(select src,dest,
distance,
case when src

[@KisaanTuber]

Thanks for sharing this question with great explanation.

[@Trevor00]

Really helpful video🤟

[@chymoney1]

Thank you sir your videos are fantastic and helpful.

[@sravankumar1767]

Nice explanation 👌 👍 👏

[@perumala4167]

We have tried in sql server
Select case when source

[@surajvishwakarma4534]

Damn!!

[@slamflix1]

sql server - SELECT substring(b.grp,1,1) as source,substring(b.grp,2,2) as destination,b.distance from
(select avg(cast(a.distance AS DECIMAL)) AS distance,a.grp from
( SELECT source,destination,distance,case when source < destination
then source+destination else destination+source end as grp
from dist ) a
group by a.grp) b;

[@ankurbhambri9199]

My solution based on a small trick
with cte as (
select
*,
case when src < dest then concat(src, ',' ,dest) else concat(dest,',' , src) end as new_col
from
src_dest_dist
)
select
split_part(new_col, ',', 1) AS Src,
split_part(new_col, ',', 2) AS Dest,
avg(distance)
from cte
group by new_col

[@varaprasad6608]

Hope this approach also works fine
select left([route],1) as src, Right([route],1) as dst, avg_distance from
(select AVG(distance) as avg_distance,
case
when src < dest then src+'-'+ dest
when src > dest then dest+ '-' + src
else src+'-'+ dest
end as [route]
from src_dest_distance
group by case
when src < dest then src+'-'+ dest
when src > dest then dest+ '-' + src
else src+'-'+ dest
end) as a

[@srushtiOm]

with cte as (
select *,
case when src < dest then concat(src,' ',dest) else concat(dest,' ',src)
end as 'Result'
from src_dest_distance_2
),
cte_1 as (
Select *,
row_number() over(partition by Result order by Result) as rnk
from cte),
cte_2 as (
Select src, dest, rnk, avg(distance) over(partition by Result order by Result) as distance_1 from cte_1)
Select * from cte_2
where rnk = 1

[@girishnaveen9059]

Great 👍

[@Kirankumar-ml1ro]

with cte as (
select
case when src

[@MukeshSahu-mn2ps]

very good 👍 👏

[@411bharath]

My solution :
with cte as(
select
case when src > dest then SRC else dest end as src,
case when src > dest then dest else src end as dest,
distance
from src_dest_distance_2)
select dest, src, AVG(Distance)
from cte group by dest, src;

[@subbuk2703]

I am waiting for your video about power bi

[@bhanuchandarpitlaravi]

Hi ,
When will you start the new batch for SQL classes

[@ezequielsaldana8279]

Hi Taufiq, thanks for your content. I show you my solution:
------------------------------------------------------------------------------------------------
with cte as(
select *,
case when src < dest then src||' '||dest else dest||' '||src end as pivot
from src_dest_dist)
select split_part(pivot, ' ', 1) as src, split_part(pivot, ' ', 2) as dist, avg(distance) as distance
from cte
group by pivot
order by pivot;
----------------------------------------------------------------------------------------------------

[@sandeepagarwal5627]

This will work if and if only there are no two common city in the set
In your case A,B,C,D are four different cities, But if you take A,B,C cities
your query will not work--
Create Table Distance(source Varchar(5), destination Varchar(5), Distance int)
begin tran
Insert into Distance values
('A','B',140),('A','B',40),('B','A',240),('B','C',40),('B','C',60),('C','B',90),('C','B',190)
--Better approach would be
With cte as (
Select *,case when source

[@zxzxzxsw]

Hi Taufiq, nice videos. Can you please suggest me a source where I can get SQL assessments and the answers for them for beginners

[@shoyebkhan4801]

Hi how to solve in query if we need the date wise txn data of current month and totat tsx count of last month in sigle query .
Please help

[@ReddyKumar-d7j]

select case when src>dest then dest else src end as source,
case when src>dest then src else dest end as destination ,
avg(distance)
from src_dest_dist
group by source,destination

[@ayush.kumar.13907]

would this solution stilll work if there were entries for A->C and C->A in original table as well?

[@saisantosh9292]

Excellent

[@saketsourav_hjp]

Hi, where can I find these types of ques along with an environment to run the queries?

[@sergeygusev6776]

select least(src,dest) src1,
greatest(src,dest) dest1,
(sum(distance)/count(1)) sd
from src_dest_dist d1
group by src1, dest1

[@miguelrufodumagoso6003]

can i compare two tables just to find if they have exact matches in their rows??

[@derejesemeaneh8972]

with cte as
(
select
case
when src = 'A' or dest = 'A' then 'A'
when src = 'C' or dest = 'C' then 'C'
end as src,
case
when dest = 'B' or src = 'B' then 'B'
when dest = 'D' or src = 'D' then 'D'
end as dest,
distance
from src_dest_dist
)
select src,dest, round(AVG(distance),2) average_distance
from cte
group by src,dest

[@chintanmistri7747]

Simple Solution
--------------------------------------------------------------------
WITH cte AS (
SELECT src,dest,distance
FROM src_dest_dist
UNION ALL
SELECT dest,src,distance
FROM src_dest_dist
)
SELECT src,dest,ROUND(AVG(distance)::NUMERIC,2)
FROM cte
WHERE src < dest
GROUP BY src,dest;
--------------------------------------------------------------------

[@arupchandra8602]

Hi Sir, here row_number is of no use right ?

[@dheerajgubbaala1552]

Hi Thoufiq I would like to join your class if we have any ongoing one or starting new batch

[@kaushalmzp]

great !!

[@maheshsomani43]

Hi Thoufiq,
Your query will FAIL in an edge case scenario where we have multiple destinations from the same source location.
You must add one more join condition i.e. a.dest=b.src
Thanks

[@saurabhshekhar2916]

if we replace the source and destination values by their ASCII values and create a derived column which is the sum of ASCII values of source column and destination column, then we can simply use Analytical AVG function on this derived table, as for same source and destination pairs the sum of their ASCII values will be the same, no matter what the order is

[@sim737tasmania6]

That would not scale though. For example, A-D would be equivalent to B-C. A simple CTE that uses CASE to alpha order the source and dest into 2 new columns will be very simple. Then GROUP BY against the CTE and job done.

[@Niece12334]

Plz upload oracle 19c software installation sir.

[@CassStevens]

Without the self join:
(but self join would be better if the two columns had more complex values like order numbers)
with t1 as
(select
case when src > dest then dest else src end as src,
case when src > dest then src else dest end as dest,
distance
from src_dest_dist),
t2 as
(select *,
count (*) over (partition by src) as cnt,
sum(distance) over (partition by src) as total_distance
from t1)
select distinct src, dest,
round(total_distance / cnt,2,1) as distance
from t2

[@sarunlorteerapong335]

let's see how I did!
with cte as
(select src, dest
,ascii(src) + ascii(dest) as SUMM
,avg(distance) over(partition by ascii(src) + ascii(dest)) as average
from src_dest_dist)
,cte2 as
(select src, dest, summ, average
,(select min(src) from cte as a where a.average = b.average) as newsrc
,(select max(dest) from cte as a where a.average = b.average) as newdest
from cte as b)
select newsrc, newdest, average
from cte2
group by newsrc, newdest, average

[@balureddy8560]

(t1.tot_dist + t2.tot_dist), here t1 and t2 are same copy of tables only right? how we can get 2 different values and sum total_dist?

[@balureddy8560]

can @TFQ or any one clear me this pls?

[@kirankumarj8229]

@@balureddy8560 Self join is used to join the table itself.Here t1 is left table t2 is right table.both tables are same only.

[@setsunasoon3470]

t1 and t2 are the same copies of the original table, but during self-join process there is this condition where row_number for table2 > row_number for table 1.
As a result, within the merged table of t1 and t2, t1 on the left will have different route compared to t2 (eg: row1 t1 route A -> B vs row1 t2 route B -> A).

[@ukkashs444]

Which is the best platform to search SQL related jobs??

[@subbuk2703]

Hi bro tell me about power bi & explain end to end plzzzz

[@chitralekhbarve7513]

why we used count(1) ?

[@cemanivannan27]

select sr,dt,avg(distance) from(select case when src

[@dipalipatil7917]

with cte as(
select least(source,destination)source,greatest(source,destination)destination,distance from sce74
)
select source,destination,round(avg(distance),2) from cte group by destination,source

[@asavlogs8446]

Sir SQL pe hindi language me video banayiye

[@hiralalpatra500]

with cte as(select src,dest,distance from src_dest_distance_2
union
select dest,src,distance from src_dest_distance_2)
,cte2 as (select src,dest,avg(distance) as avg_ from cte
group by src,dest)
select c2.src,c2.dest,c1.avg_ from cte2 as c1
join
cte2 as c2
on c1.avg_=c2.avg_ and ascii(c1.dest)>ascii(c2.src)

[@sodiqafolayan4921]

@techTFQ, What do you think about this query
SELECT src1,
dest1,
ROUND(new_dist/trip_count) avg_dist
FROM
(SELECT LEAST(src, dest) src1,
GREATEST(src, dest) dest1,
COUNT(*) trip_count,
SUM(distance) new_dist
FROM src_dest_dist
GROUP BY 1, 2) sub1

[@indianfoodie6745]

select DISTINCT
least(source, destination) S1,
greatest(source,destination) D2,sum(distance) as Distance,count(*) Counting,
sum(Distance)/Count(distance) AVG
from table
group by S1,D2; this also can work right?

[@avi8016]

Thanks for the detailed explanation sir 💯

[@bharatchaudhury]

with src_dest as
(
Select src , dest , distance , row_number() over() as rn
From
(select src , dest , distance
from src_dest_distance
union
Select dest, src, distance
from src_dest_distance)X
)


Select sd1.src , sd1.dest , round(avg(sd1.distance),2) as avg_distance
from src_dest sd1 , src_dest sd2
where sd1.src = sd2.dest
and
sd1.dest = sd2.src
and sd1.rn < sd2.rn
group by 1,2

[@b_rizzle4808]

Thoufiq did it better, but here's my solution:
with grp_cte as
(select distinct
src||dest as grped
from src_dest_dist
order by 1)
, cte_with_num as
(select grp_cte.*
, row_number() over(order by grped) as row_num
from grp_cte)
, grping as
(select *
from cte_with_num
where mod(row_num,2) = 1)
select substr(grped,1,1) as src, substr(grped,2,1) as dest, round(avg(distance),2)
from src_dest_dist
join grping on src||dest=grped or dest||src=grped
group by substr(grped,1,1), substr(grped,2,1)
order by 1

[@b_rizzle4808]

actually, i came up with a better/more concise way than my first attempt:
with cte as
(select s.*,case when src < dest then src||dest else dest||src end as grp
from src_dest_dist s)
select substr(grp,1,1) as src, substr(grp,2,1) as dest, avg(distance) as average_distance
from cte
group by substr(grp,1,1), substr(grp,2,1)
order by 1

[@jesusyaya755]

Do this work? I get the same result with less code. First I create another source and destination column, which are ordered alfabetically (the first in source and the last in destination)
SELECT
IIF(SourceDestination,Source,Destination) AS 'Destination_2' ,
AVG(Distance) AS Avg_Dist
FROM Table_Name
GROUP BY IIF(SourceDestination,Source,Destination)

[@vijaypalmanit]

Putting calculation in group by clause is not efficient

[@hamzaahmad6741]

Hi,
If we insert this row already in the given data. The provided solution will crash .
insert into src_dest_distance_2 values ('C', 'A', 21);

[@AxDhan]

I'm just learning, but I think hard baked answers like in this video are poorly done, I did something like this:
select if(SOURCES < DESTINATION, SOURCES, DESTINATION) as S, if(SOURCES < DESTINATION, DESTINATION, SOURCES) as D, avg(DISTANCE) as DISTANCE from distance_between_location
group by S, D;
changed source for sources, idk why it was giving me an error, this work with ('C', 'A', 21) too

[@varunparuchuri9544]

Thop 🔥 🔥

[@sahithi1287]

please t1.dest=t2.src then it is applicable to all scenarios

[@AdilShahzad-l7j]

WITH cte1 AS (
SELECT CASE WHEN src < dest THEN src || dest ELSE dest || src END AS pair, distance
FROM distance)
SELECT SUBSTRING(pair, 1, 1) AS source, SUBSTRING(pair, 2, 1) AS destincation, AVG(distance) avg_dist
FROM cte1
GROUP BY pair

[@viethuynh5453]

Solution only work with given example. If we have D to A in your dataset, we need to add extra condition.

[@sabarinathan6220]

Bro,Can we join your SQL course now?

[@abhishekgole5075]

Hi Taufiq, Please let me know if my soln is correct or not but with this soln i m getting the desired result.
with cte as (
select replace(replace(src,'B','A'),'D','C') as srce,replace(replace(dest,'A','B'),'C','D') as deste, distance
from src_dest_dist)
select srce, deste, AVG(distance) as dist
from cte
group by srce,deste

[@saikatde6343]

WITH cte AS
(
SELECT
CASE WHEN src < dest THEN src ELSE dest END AS source
, CASE WHEN src > dest THEN src ELSE dest END AS destination
, distance
FROM src_dest_distance_2
)
SELECT source ,
destination ,
CAST(AVG(distance) AS DECIMAL(10,2)) AS distance from cte
GROUP BY source , destination;

[@kmac2411]

/*
sql server solution for scenarios where there is no row with reverse route present
*/
with tmp as
(
select 'A' src, 'B' DEST, 21 dist
union all
select 'B' src, 'A' DEST, 28 dist
union all
select 'A' src, 'B' DEST, 19 dist
union all
select 'C' src, 'D' DEST, 15 dist
union all
select 'C' src, 'D' DEST, 17 dist
union all
select 'D' src, 'C' DEST, 18dist
union all
select 'D' src, 'C' DEST, 16.5 dist
union all
select 'E' src, 'F' DEST, 20 dist
)
,cities as(
select src cities from tmp union select dest from tmp
),ln as
(select ROW_NUMBER()over (order by cities )rnk, * from cities)
,fin as(
select t.*
,case when s.rnk < d.rnk then s.cities else d.cities end src_new
,case when s.rnk > d.rnk then s.cities else d.cities end dest_new
from tmp t
inner join ln s on s.cities = t.src
inner join ln d on d.cities = t.DEST)
select src_new,dest_new, avg(dist)dist from fin group by src_new,dest_new

[@Vivek-pradhan]

Can you teach us SQL .im from india

[@bhaskarthupakula4275]

What is the starting salary for a SQL ,And how is growth?

[@raknoknak]

Your question makes no sense, there is no such job as "a SQL"

[@mattiaslind6854]

Why overcomplicate the problem...
SELECT
CASE WHEN src < dest THEN src ELSE dest END,
CASE WHEN src < dest THEN dest ELSE src END,
CAST(AVG(distance) as decimal(18,1))
FROM src_dest_hist
GROUP BY
CASE WHEN src < dest THEN src ELSE dest END,
CASE WHEN src < dest THEN dest ELSE src END

[@ontipulisudhakar6951]

Execilent

[@aniketpatil3989]

SELECT SOURCE1, DESTINATION, TRUNC(AVG(DISTANCE),2) AS AVG_DISTANCE
FROM(
SELECT LEAST(SOURCE1,DESTINATION) AS SOURCE1,
GREATEST(SOURCE1,DESTINATION) AS DESTINATION,
DISTANCE
FROM DISTANCE_TABLE)
GROUP BY SOURCE1,DESTINATION;

[@rajattalnikar6507]

create table distance (source varchar(10),
destination varchar(10),
dist decimal(5, 2));
insert into distance
values ('A','B',21.00),
('B','A',28.00),
('A','B',19.00),
('C','D',15.00),
('C','D',17.00),
('D','C',16.50),
('D','C',18.00);
with temp as
(select *,
1 as cnt
from distance), temp2 as
(select source,
destination,
sum(dist) as dist,
sum(cnt) as cnt
from temp
group by source,
destination),
temp3 as
(select *,
row_number() over (
order by source,
destination) as rn
from temp2)
select t1.source,
t1.destination,
(t1.dist+t2.dist)/(t1.cnt+t2.cnt) as average
from temp3 t1
inner join temp3 t2 on t1.source=t2.destination
and t1.destination=t2.source
and t1.rn

[@devarapallivamsi7064]

My solution:
WITH row_nums AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY src) AS rn
FROM src_dest_dist
)
, main_tab AS (
SELECT srd_1.*,
srd_2.dest AS src_2,
srd_2.src AS dest_2,
srd_2.distance AS dist_2
FROM
row_nums srd_1
JOIN row_nums srd_2 ON srd_1.src = srd_2.dest
AND srd_1.rn < srd_2.rn),
tab_1 AS (
SELECT src,dest,distance
FROM main_tab
),
tab_2 AS (
SELECT
src_2 AS src,
dest_2 AS dest,
dist_2 as distance
FROM main_tab
),
unioned AS (
SELECT src, dest, distance FROM tab_1
UNION
SELECT src ,dest, distance FROM tab_2
)
select src,dest,ROUND(AVG(distance)::decimal,2)
FROM unioned
GROUP BY 1,2
I understand that it is lengthy, but is definitely intuitive.

[@rameshthanikonda7027]

Hi Toufiq,
I attended sql interview and the interviewer asked me below sql query. Kindly explain and help me to crack next interview. Thanks
SQL:
Table A with column C1 has 7 records I. E
1,1,1,1,1,Null, Null and
Table B with column C2 has 5 records I. E
1,1,1,2,Null. How records we will get by using inner join, left join, right join and full join.

[@sridharanjb2928]

15,17,17,19

[@vishalkhare4654]

Inner join- 15
Left join- 17
Right join- 17
Full outer join- 19
Cross join- 35

[@wellywalker8397]

select 'A' as source,'B' as destination,
round(avg(distance),2) as distance
from src_dest_distance_2 where src='A' or src='B'
union
select 'C' as source,'D' as destination,
round(avg(distance),2) as distance
from src_dest_distance_2 where src='C' or src='D'

[@akash-uy3uh]

much easier way :
with akash as (select *,case
when src in ('A','B') and dest in ('B','A') then 'o'
when src in ('C','D') and dest in ('C','D') then 'p'
end as 'op'
from src_dest_dist) select src,dest,avg(distance) from akash
group by op;

[@shabanashabana4603]

👍

[@venkateshdeekonda8665]

My Solution:
declare
cursor c is select * from distance;
Count_1 number;
count_2 number;
d_1 number;
d_2 number;
f_loc varchar2(1);
t_loc varchar2(1);
d_avg number;
begin
for i in c
loop
if (i.from_loc nvl(f_loc,'X') and i.to_loc nvl(t_loc,'X'))
and (i.from_loc nvl(t_loc,'X') and i.to_loc nvl(f_loc,'X'))
then
select count(*),sum(distance)
into count_1,d_1
from distance
where from_loc = i.from_loc
and to_loc = i.to_loc;
select count(*),sum(distance)
into count_2,d_2
from distance
where from_loc = i.to_loc
and to_loc = i.from_loc;
d_avg := (d_1+d_2)/(count_1+count_2);
dbms_output.put_line('Average distance from '||i.from_loc||' to '||i.to_loc||' is '||d_avg);
end if;
f_loc := i.from_loc;
t_loc := i.to_loc;
end loop;
end;
Output:
Average distance from A to B is 20
Average distance from C to D is 16.625
Average distance from E to F is 33

[@joncannaday250]

CREATE TABLE maps (
rownum INTEGER PRIMARY KEY AUTOINCREMENT,
src TEXT,
dest TEXT,
distance NUMERIC);

INSERT INTO maps (src, dest, distance)
VALUES ('A','B',21),
('B','A',28),
('A','B',19),
('C','D',15),
('C','D',17),
('D','D',16.5),
('D','C',18);
SELECT
CASE
WHEN (src='A' OR src='B') = 1
THEN 'A' ELSE 'C'
END AS source,
CASE
WHEN (src="A" OR src="B")=1
THEN 'B' ELSE 'D'
END AS destination,
ROUND (SUM(distance) / COUNT(*) , 2) AS distance
FROM maps
GROUP BY source;

[@nikolaybaranov2213]

Too difficult! A better way is using string comparison. This works for example in MS SQL Server and MySQL
WITH src_dest_dist_ord
AS
(
SELECT src as P1
,dest as P2
,distance
FROM src_dest_dist
WHERE src < dest
UNION ALL
SELECT dest as P1
,src as P2
,distance
FROM src_dest_dist
WHERE src > dest
)
SELECT P1, P2, Avg (distance)
FROM src_dest_dist_ord
GROUP BY P1, P2

[@nabinagoswami1261]

WITH Final As (
WITH CTE AS(
SELECT *, sum(distance) as TD ,count(*) AS TR FROM src_dest
GROUP BY src,dest
ORDER BY src)
SELECT * , ROW_Number() OVER(order by src) as RNK FROM CTE )
SELECT S1.src ,S1.dest , ROUND((S1.TD+S2.TD)/(S1.TR+S2.TR),2) AS Average_Distnace
FROM Final S1
JOIN Final S2
ON S1.RNK

[@HARSHRAJ-gp6ve]

with cte as(
SELECT * FROM src_dest_dist ORDER BY src
),cte1 as(
select src,dest,SUm(distance) AS sum1,COUNT(*) as c1,ROW_NUMBER()OVER() as x1 FROM cte GROUP BY src,dest
),cte2 as(
select src as s1,dest as des1,SUm(distance) AS sum2,COUNT(*) as c2,ROW_NUMBER()OVER() AS x2 FROM cte GROUP BY s1,des1
),cte3 as(
select * FROM cte1 join cte2 ON cte1.dest=cte2.s1 and cte1.src=cte2.des1 and x1

[@neelanshunisingh971]

Here is my sol:
SELECT least(src,dest) as src, Greatest(src,dest) as dest, avg(distance) as avg_distance
FROM wau.src_dest_dist
group by least(src,dest), Greatest(src,dest)