Mountain Huts & Trails - SQL Interview Query 2 | SQL Problem Level "HARD"

[@chandudbg1534]

Hi techTFQ , is there any way to transfer every knowledge from ur mind to my mind, complete data transfer,, u r just woww 🔥🔥

[@Unbox9999]

Practice 😂

[@sohinibanerjee9617]

Good question

[@ravishmahajan9314]

Practice bro ....... if you havent watched his window function series let me tell you , its the best series on YT on this topic. I bet you will not find better video on this anywhere not even in official documentations. :)

[@nevergiveup3475]

You are good question 😂😂

[@sanjeetsignh]

with cte as
(
select
case when b.altitude > c.altitude then b.name else c.name end as high_hut,
case when b.altitude < c.altitude then b.name else c.name end as low_hut
from trails a
join mountain_huts b on a.hut1 = b.id
join mountain_huts c on a.hut2 = c.id
)
select a.high_hut as startpt, a.low_hut as middlept , b.low_hut as endpt
from cte a
join cte b on a.low_hut = b.high_hut
order by 1

[@Tusharchitrakar]

This is exactly how i did it too

[@sanjeetsignh]

@@travelwithme7684
The CTE selects two columns:
high_hut: It compares the altitude of two mountain huts (b and c) along each trail (a). If b.altitude is greater than c.altitude, it selects b.name as the higher hut; otherwise, it selects c.name.
low_hut: Similarly, it selects the lower hut between b and c.
It joins three tables: trails, mountain_huts (twice, aliased as b and c) to obtain the names and altitudes of the mountain huts.
Main Query:
It selects three columns: startpt, middlept, and endpt.
It joins the CTE (cte) with itself (aliased as b) on a.low_hut = b.high_hut. It orders the result by the first column (startpt).

[@sanjeetsignh]

@@Tusharchitrakar This solution is pretty straight forward without over complicating it.

[@Tusharchitrakar]

@@sanjeetsignh yes exactly, the simple trick is to switch the low and high huts in order for each record in the trails table using case when. The subsequent join becomes straightforward then. It took me only 15 mins to solve this otherwise might have taken more with more complex logic

[@mananshah7095]

This was a tricky problem where I solved it using unions and multiple joins but your solution makes a lot more logical sense.
Thank you for this! :)

[@sweetysweetyvghb]

I too solved using unions but for large datasets it's not good performance wise I think ..

[@T5AGamerz]

You and ankit Bansal are best In sql

[@nambidasan6314]

Solution: The first cte determines the altitude of hut1 and hut2 and the second cte (cte_bidirectional) is making use of the bidirectional condition (Note that all trails are bidirectional) as defined in the problem statement here to swapthe path (hut2 hut1), because the reverse route is possible which is based on decreasing altitude.
;with cte as
(
select
t.hut1, (select alt.altitude from mountain_huts alt where t.hut1 = alt.id) as hut1_alt,
t.hut2, (select alt.altitude from mountain_huts alt where t.hut2 = alt.id) as hut2_alt
from trails t
),
cte_bidirectional as
(
select
case when hut2_alt > hut1_alt then hut2 else hut1 end as hut1,
case when hut1_alt < hut2_alt then hut1 else hut2 end as hut2
from cte c
)
Select t1.hut1 as Startpt, t1.hut2 as MidPt, t2.hut2 as EndPt from cte_bidirectional t1 inner join cte_bidirectional t2 on t1.hut2 = t2.hut1

[@dheerajsinghdangwal453]

with temp as (
select * from(
select *,case
when a2=b1 then concat(b2,'-',a2,'-',a1)
when a1=b1 and a2!=b2 then concat(a2,'-',a1,'-',b2)
when a1=b2 then concat(b1,'-',a1,'-',a2)
when a2=b2 and a1!=b1 then concat(a1,'-',a2,'-',b1)
end as root
from (
select a.hut1 as a1,a.hut2 as a2,b.hut1 as b1 ,b.hut2 as b2 from trails a join trails b)p)b
where root is not null
order by root),
temp1 as (
select m.name as start_point,h.name as mid_point,mh.name as last_point,m.altitude ma,h.altitude ha,mh.altitude mha,concat(m.id,'-',h.id,'-',mh.id) as idc
from mountain_huts m join mountain_huts h join mountain_huts mh )
, temp2 as (
select * from temp1 t1 join temp t on t1.idc=t.root
where ma>ha)
select start_point,mid_point,last_point from temp2 where ha>mha ;

[@nairitjoshi5933]

Lots of thanks to you sir for bringing such amazing content that keeps my mind running to build logic in sql.
Attached my answer :- Giving perfect result, but definitely not the most optimized query.
Logic building -15 min, query writing- 5mins
ORACLE SQL DEVELOPER
select A.* from (
select A.*,b.name as endp_name,b.altitude as endp_altitude from (
select A.*,b.name as midp_name,b.altitude as midp_altitude from (
select A.*,b.name as startp_name,b.altitude as startp_altitude from (
select A.* from (
select a.hut1 as startp,a.hut2 as midp, b.hut2 as endp from sql_complex.trails A
join sql_complex.trails B
on a.hut2=b.hut1 )A
union
select A.* from (
select a.hut2 as startp,a.hut1 as midp, b.hut1 as endp from sql_complex.trails A
join sql_complex.trails B
on a.hut1=b.hut2 )A
union
select A.* from (
select a.hut2 as startp,a.hut1 as midp, b.hut2 as endp from sql_complex.trails A
join sql_complex.trails B
on a.hut1=b.hut1
where a.hut2!=b.hut2)A
union
select A.* from (
select a.hut1 as startp,a.hut2 as midp, b.hut1 as endp from sql_complex.trails A
join sql_complex.trails B
on a.hut2=b.hut2
where a.hut1!=b.hut1)A)A
join sql_complex.mountain_huts B
on a.startp=b.id)A
join sql_complex.mountain_huts B
on a.midp=b.id)A
join sql_complex.mountain_huts B
on a.endp=b.id)A
where A.startp_altitude>a.midp_altitude and a.midp_altitude>a.endp_altitude

[@makarsh29]

btw , i got it ...
but i will do this question after 2 months , because i realise : 'meri umar is tarah queries likhne ki nhi hai '
vaise @techTFQ , 1 st wala question kam horrible tha kya jo ise de diye

[@Behappy-ft5yc]

THANK YOU SO MUCH !!!!, IT MEANS A LOT FOR US

[@ashwinkarki8112]

I solved using the solution :
with cte as (
select t.hut1,m.name as source_hrt,m.altitude as source_altitude,t.hut2,m1.name as inter_hrt,m1.altitude
as inter_altitude from trails t join mountain_huts m on
t.hut1=m.id join mountain_huts m1 on m1.id=t.hut2
),
route1 as (
select
c.source_hrt as first_route,
c.inter_hrt as second_route,
c1.source_hrt as final_route
from cte c join cte c1 on c.hut2=c1.hut2
where c.source_altitude>c.inter_altitude
and c.inter_altitude>c1.source_altitude
)
--select * from route1
,
cte2 as (
select inter_hrt as source_hrt,inter_altitude as source_Altitude,
hut2 as hut1,hut1 as hut2,source_hrt as inter_hrt,source_altitude as inter_altitude
from cte
)
,
route2 as (
select c.source_hrt as first_route,
c.inter_hrt as second_route,
c1.source_hrt as final_route
from cte2 c join cte2 c1 on c.hut2=c1.hut2 where c.source_altitude>c.inter_altitude
and c.inter_altitude>c1.source_altitude
),
route3 as (
select
c.source_hrt as first_route,
c.inter_hrt as second_route,
c1.inter_hrt as final_route
from cte c join cte c1 on c.hut2=c1.hut1
where c.source_altitude>c.inter_altitude
and c.inter_altitude>c1.inter_altitude
)
select * from (
select * from route3
UNION ALL
select * from route2
UNION ALL
select * from route1) x order by 1

[@techatnyc7320]

Wow, this is so perfect. Thanks

[@효캉]

with cte as (
select Hut1, Hut2
from trails
UNION
select Hut2 as Hut1,Hut1 as Hut2
from trails),
cte2 as (select c1.Hut1, c1.Hut2, c2.Hut2 as Hut3
from cte c1
join cte c2 on c1.Hut2 = c2.Hut1
where c1.Hut1 != c1.Hut2 and c1.Hut2 != c2.Hut2 and c1.Hut1 != c2.Hut2)
select mh1.Name, mh2.Name, mh3.Name
from cte2 c
join mountain_huts mh1 on c.Hut1 = mh1.Id
join mountain_huts mh2 on c.Hut2 = mh2.Id
join mountain_huts mh3 on c.Hut3 = mh3.Id
where mh1.Altitude > mh2.Altitude and mh2.Altitude > mh3.Altitude
This can be intuitive and easier approach!

[@juanromantorres5063]

Thanks You for share !!! Great explanation !

[@ongole_boy6346]

If you have time please start plsql course from basic to advance

[@anjalikumar9193]

Hi , it's been 2-3 months since I started learning SQL,and to be honest I found this problem very hard for me. I want to know the level of the problem . (is it easy, medium or hard?)

[@ershanhaque9907]

Hard

[@only4job-pi3qd]

read title...

[@anjalikumar9193]

​@@only4job-pi3qdyup I guess he updated this now

[@sivakrishnasriram4782]

Thank You.

[@sethvishu]

My query is much simplar and lot less complex hopefully you will look this once.
With cte as (select
Case when mh1.altitude > mh2.altitude then mh1.name
else mh2.name End as start_hut,
Case when mh1.altitude < mh2.altitude then mh1.name
else mh2.name End as end_hut from trails t
JOIN mountain_huts mh1 ON mh1.id = t.hut1
JOIN mountain_huts mh2 ON mh2.id = t.hut2)
Select c1.start_hut as start_pt, c1.end_hut as middle_pt,
c2.end_hut as end_pt from cte c1
join cte c2 on c1.end_hut = c2.start_hut

[@aish000]

In the last line,
Why did you do
C1.end_hut = C2.start_hut
Why not
C1.start_hut = C2.end_hut

[@bujin1977]

This was a pretty damn good challenge! It looked pretty complicated on face value, but wasn't as hard as I expected it to be. I managed to solve it in about ten minutes. My solution was slightly different, and going by the statistics, slightly less efficient. But I'm quite happy that I came up with a solution that worked pretty quickly.

[@navneetkhare97]

with cte as
(
select A.hut1, A.hut2 from trails A
union
select A.hut2, A.hut1 from trails A
)
select M.name as hut1,M2.name as hut2,M3.name as hut3
from cte A
Inner JOIN cte B
ON
A.hut2=B.hut1
and A.hut1B.hut2
INNER JOIN mountain_huts M
ON A.hut1 = M.id
INNER JOIN mountain_huts M2
on A.hut2 =M2.id
INNER JOIN mountain_huts M3
on B.hut2= M3.id
WHERE
M.altitude>M2.altitude AND
M2.altitude>M3.altitude
order by 1

[@ContentRoomB]

Couldn't solve it on my own but can now with the solution. Looking forward to finally solving one by myself. Thanks TFQ

[@PravinKumar-v9e]

With trail_path(trail_start, trail_end) as (
Select
CASE WHEN hut1_altitude > hut2_altitude then hut1
ELSE hut2
END AS trail_start,
CASE WHEN hut1_altitude < hut2_altitude then hut1
ELSE hut2
END AS trail_end
FROM
(Select name as hut1, altitude as hut1_altitude, ROW_NUMBER() OVER () AS ID
from trails
left join mountain_huts
on hut1 = id) L
INNER JOIN
(Select name as hut2, altitude as hut2_altitude , ROW_NUMBER() OVER () AS ID
from trails
left join mountain_huts
on hut2 = id) R
on L.ID = R.ID)
SELECT t1.trail_start as startpt, t1.trail_end as middlept, t2.trail_end as endpt FROM trail_path as t1
cross join trail_path as t2
where t1.trail_end = t2.trail_start
order by 1

[@PravinKumar-v9e]

my solution:
With trail_path(trail_start, trail_end) as (
Select
CASE WHEN hut1_altitude > hut2_altitude then hut1
ELSE hut2
END AS trail_start,
CASE WHEN hut1_altitude < hut2_altitude then hut1
ELSE hut2
END AS trail_end
FROM
(Select name as hut1, altitude as hut1_altitude, ROW_NUMBER() OVER () AS ID
from trails
left join mountain_huts
on hut1 = id) L
INNER JOIN
(Select name as hut2, altitude as hut2_altitude , ROW_NUMBER() OVER () AS ID
from trails
left join mountain_huts
on hut2 = id) R
on L.ID = R.ID)
SELECT t1.trail_start as startpt, t1.trail_end as middlept, t2.trail_end as endpt FROM trail_path as t1
cross join trail_path as t2
where t1.trail_end = t2.trail_start
order by 1

[@BlackSouL24]

I loved it❤

[@pharandesuraj6031]

Kadakk ❤

[@kanimozhipanneerselvam3017]

Perfect!!

[@MohitKumar-ex1pk]

Can any one explain how nested join will result in data loss?
i did the nested join in my solution and getting the expected output.

[@manu_goyal007]

+1

[@lalitpant6914]

My solution:
with cte as(
select hut1,h.name as startpt,h.altitude as hut1_Alt,hut2,h2.name as endpt,h2.altitude as hut2_alt
from trails T
join mountain_huts h on h.id=t.hut1
join mountain_huts h2 on h2.id=t.hut2
),
cte1 as(
select * from cte
union all
select hut2 as hut1,endpt as startpt,hut2_alt as hut1_alt,hut1 as hut2,startpt as endpt,hut1_alt as hut1_alt from cte
)
select a.startpt,a.endpt as middlept,b.endpt from cte1 A
join cte1 B
on a.hut2=b.hut1
where A.hut1_alt>a.hut2_alt and b.hut1_alt>b.hut2_alt

[@pveeranjireddy8959]

Looks these are complex sql queries , not easy to bring solution i feel

[@PrakashUkkaravel-b6f]

You could just use a left join 1 tiems in the start 12:42, it would do the same.

[@umangbhatnagar1415]

No left join won't work. Left joins would work to print all the remaining data based on the single particular column you applied a join condition on. With the solution above, they are joining two tables, based on Join condition which is on another column in the second join condition. Hope, this helps !!
Fundamentally, left join is altogether a different concept from what is solved by @techTFQ above.

[@zeeshanahmed9719]

My solution:
with half_track as
(select m1.id as point_1, m1.name as strt_point, m2.id as point_2, m2.name as mid_point, m2.altitude as altitude from mountain_huts m1 join mountain_huts m2
where m1.altitude > m2.altitude),
full_track as
(select point_1, strt_point, point_2, mid_point, ht.altitude, m3.id as point_3, m3.name as end_point, m3.altitude as altitude_3 from half_track ht join mountain_huts m3
where ht.altitude > m3.altitude),
available_tracks as
((select concat(hut1, hut2) as avail_roots from trails)
union
(select concat(hut2, hut1) as avail_roots from trails)),
verified_track as
(select point_1, strt_point, point_2, mid_point, point_3, end_point, concat(point_1, point_2) as root_1, concat(point_2, point_3) as root_2 from full_track)
select strt_point, mid_point, end_point from verified_track
where root_1 in (select * from available_tracks)
and (root_2 in (select * from available_tracks));

[@StephenSanchez-p7h]

Harris Richard Martinez Richard Perez Deborah

[@NahlaAhmed-jf5to]

thank you

[@makarsh29]

can we use recursive cte to solve this ??
anyone ?

[@Ansuyapanwar]

Are these type of questions asked to freshers as well ?

[@gabrielcarbajalcarbajal3025]

select a.name as startpt,b.name as middlept ,c.name as endpt from mountain_huts a join mountain_huts b join mountain_huts c
where b.altitude

[@sujaa1000]

Thank you Tofiq, very very helpful to learn all the concepts, and amazing logic. I always have something to learn from your channel, one of the best channel.

[@Steven-jf4cs]

I know I'm late for the 30 day challenge in real time. This is a very useful series - joined your Discord and subscribed to your channel

[@sampri22]

Hey @techTFQ -- how can I get the dataset? I am not able to join discord, it says: invite invalid. Why do you keep the dataset on discord and not on your blog?

[@NEHAKHANZODE-p8p]

Thank you so much:-)

[@sahilummat8555]

;with cte as (
select hut1,hut2 from trails
union
select hut2,hut1 from trails
),cte2 as (
select t1.*,t2.hut2 as hut3 from cte t1
inner join cte t2 on t1.hut2=t2.hut1)
select
mh.name as startup
,mh1.name as middle_point
,mh2.name as end_point
from cte2 c
inner join mountain_huts mh on c.hut1=mh.id
inner join mountain_huts mh1 on c.hut2=mh1.id
inner join mountain_huts mh2 on c.hut3=mh2.id
where mh.altitude > mh1.altitude and mh1.altitude>mh2.altitude

[@jagrititiwari437]

Confusing 😮😮😮😮

[@adityavamsi12]

Great great explanation as well as problem ❤

[@akankshamerupula0607]

I used MySQL to solve this:
with cte as (SELECT
hut1,max(CASE WHEN hut1 = id THEN name END) AS n1,
max(CASE WHEN hut1 = id THEN altitude END) AS a1,
hut2,max(CASE WHEN hut2 = id THEN name END) AS n2,
max(CASE WHEN hut2 = id THEN altitude END) AS a2
FROM
trails
JOIN
mountain_huts
ON
hut1 = id OR hut2 = id
group by hut1,hut2),
cta as (select
case when a1>a2 then n1 else n2 end as newn1,
case when a1>a2 then a1 else a2 end as newa1,
case when a1>a2 then n2 else n1 end as newn2,
case when a1>a2 then a2 else a1 end as newa2
from cte)
select
a.newn1,a.newn2,b.newn2 as newn3
from cta a join cta b
on a.newn2=b.newn1 and a.newa2>b.newa2
order by newn1;

[@Satish_____Sharma]

Solved by using MYSQL
with cte as (select t.hut1,mh.name as mh_name,mh.altitude as mh_altitude,t.hut2,mh1.name as mh1_name,mh1.altitude as mh1_altitude
from trails t
inner join mountain_huts mh on t.hut1=mh.id
inner join mountain_huts mh1 on t.hut2=mh1.id)
,cte1 as (select case when mh_altitude>mh1_altitude then hut1 else hut2 end as hut1,
case when mh_altitude>mh1_altitude then mh_name else mh1_name end as start_loc,
case when mh_altitude>mh1_altitude then mh_altitude else mh1_altitude end as start_alti,
case when mh_altitude>mh1_altitude then hut2 else hut1 end as hut2,
case when mh_altitude>mh1_altitude then mh1_name else mh_name end as end_loc,
case when mh_altitude>mh1_altitude then mh1_altitude else mh_altitude end as end_alti
from cte)
select c1.start_loc as startpt,c1.end_loc as middlept,c2.end_loc as endpt
from cte1 as c1
inner join cte1 as c2 on c1.hut2=c2.hut1

[@balling220]

Could you include "Ollivander's Inventory" from Hackerrank

[@aarizaziz4380]

@10:58 any idea why joining hut2 doesn't give us the correct result?

[@arideepchakraborty5642]

Excellent

[@shivinmehta7368]

with cte as (
select t.*,
h1.name as h1_name, h1.altitude as h1_alt ,
h2.name as h2_name, h2.altitude as h2_alt
from trails t
left join mountain_huts h1 on t.hut1=h1.id
left join mountain_huts h2 on t.hut2=h2.id
)
, cte1 as (
select
case when h1_alt>=h2_alt then h1_name else h2_name end as high_hut,
case when h1_alt=h2_alt then hut1 else hut2 end as hhid,
case when h1_alt

[@NEHAKHANZODE-p8p]

You and Ankit Bansal can makes sql easy 🙂

[@AbhishekKumar-b1j1x]

Great video ❤❤❤
Here the a tricky question...
Calculate the percentage change in revenue from the previous month to the current month.
And here is the solution:-
WITH monthly_revenue AS (
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
SUM(order_amount) AS revenue
FROM
orders
GROUP BY
YEAR(order_date), MONTH(order_date)
)
SELECT
(current_month.revenue - previous_month.revenue) * 100.0 / previous_month.revenue AS percentage_change
FROM
monthly_revenue current_month
JOIN
monthly_revenue previous_month ON current_month.order_year = previous_month.order_year
AND current_month.order_month = previous_month.order_month + 1
WHERE
current_month.order_year = YEAR(CURRENT_DATE)
AND current_month.order_month = MONTH(CURRENT_DATE);
But I don't know whether this is correct or not
Please explain this this kind of question.

[@harshSingh-if4zb]

you can use lag function to compare previous month revenue with current month.

[@varunas9784]

Thanks for sharing Thoufik!
Here's my attempt at it (SQL server)
=======================================================
with cte as (select
case when m1.altitude - m2.altitude >0 then m1.name
else m2.name end as startpt,
case when m1.altitude - m2.altitude

[@HarvinderSingh-e1p]

Really thoufiq u r the king of SQL

[@prasathkumar2602]

Thanks you for ypur explanation

[@ehsanshakeri620]

Thank You. that was a great problem

[@splendidabhi]

#DAY2 OF #30DaySQLQueryChallenge

[@livelovelaugh4050]

Thank you.. Thank you so much sir.

[@venkateshmustipally]

Hi techTFQ, you did not check the altitude condition in the last cte self join i.e., from mid_point to end_point ?

[@pinkfloyd2642]

The mid point is always a start hut in one of the rows. And if it is a start hut somewhere, its corresponding end hut would always be at lower altitude because that is how they are arranged in the cte. I hope I am getting your question right.

[@KMONONI]

I used Lead Window Function. Lol Gee Whiz It was difficult:
with cte_start_mid_data as (
select case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) > altitude then lead(name) over (partition by hut1,hut2 order by altitude asc) else name end 'StartPoint'
, case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) > altitude then lead(id) over (partition by hut1,hut2 order by altitude asc) else id end 'StartHut'
, case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) > altitude then lead(altitude) over (partition by hut1,hut2 order by altitude asc) else altitude end 'StartHut_Altitude'
, case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) < altitude then lead(name) over (partition by hut1,hut2 order by altitude asc) else name end 'MidPoint'
, case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) < altitude then lead(id) over (partition by hut1,hut2 order by altitude asc) else id end 'MidPointHut'
, case when lead(altitude) over (partition by hut1,hut2 order by altitude asc) < altitude then lead(altitude) over (partition by hut1,hut2 order by altitude asc) else altitude end 'MidPointHut_Altitude'
,row_number() over(partition by hut1,hut2 order by altitude asc ) rnk
,altitude
from mountain_huts
join trails ON id = hut1 or hut2 = id
)
select a.StartPoint/*,a.StartHut*/, a.MidPoint/*, a.MidPointHut*,a.StartHut_Altitude,a.MidPointHut_Altitude*/, b.MidPoint 'End Point'
from cte_start_mid_data a
join cte_start_mid_data b ON a.MidPointHut = b.StartHut
where a.rnk = 1 and b.rnk =1

[@marcm8745]

Great explanation!

[@jayavinayak2593]

Thank You.

[@umesh6600]

great job bro..

[@samratsingha8515]

very nice

[@pharandesuraj6031]

Thanks sir

[@Hsalz]

👏

[@vinayakyerekar762]

🎉🎉

[@adim1212]

Hi Sir, I followed somewhat similar strategy, even though in all 3 results are similar but the order is different.the difference was in naming the column s at different levels of cte.
Could you please explain why there is a difference in order of the result. e.g. you got Dakonat,Natisa,Gajantut,Dakonat in 1st column and their corresponding values in other columns. In my resultset, I got Dakonat,Dakonat,Gajntut,Natisa and their corresponding values.

[@fathimafarahna2633]

🔥

[@apurvasaraf5828]

please give the create table statement

[@ravishmahajan9314]

Its in the blog link in the description.

[@insidehead]

Hi how we can convert this data into sql server? Any trick?

[@malcorub]

Ez peazy. Just CREATE TABLE and INSERT INTO the values. The data sets are small enough that its simple to do. Plus it's good practice for creating tables and data. 😃

[@insidehead]

@@malcorub in case i have good enough dataset then .

[@gphanisrinivasful]

@@insidehead Import from excel or csv. There are multiple import options available in all SQL Server Management software.

[@vijayavenkatasatyaavinashk7760]

This is my solution:
first find the triplets
then filter the routes.
with triplet as (
SELECT
case when a.hut1=b.hut1 then a.hut2
when a.hut1=b.hut2 then a.hut2
when a.hut2=b.hut1 then a.hut1
when a.hut2=b.hut2 then a.hut1
end as starthut,
case when a.hut1=b.hut1 then a.hut1
when a.hut1=b.hut2 then a.hut1
when a.hut2=b.hut1 then a.hut2
when a.hut2=b.hut2 then a.hut2
end as middlehut,
case when a.hut1=b.hut1 then b.hut2
when a.hut1=b.hut2 then b.hut1
when a.hut2=b.hut1 then b.hut2
when a.hut2=b.hut2 then b.hut1
end as endhut
FROM trails a
JOIN trails b on (a.hut1=b.hut1 or a.hut1=b.hut2 or a.hut2=b.hut1 or a.hut2=b.hut2)
)
select b.name as startpt, c.name as middlept, d.name as endpt from triplet a
join mountain_huts b on a.starthut = b.id
join mountain_huts c on a.middlehut = c.id
join mountain_huts d on a.endhut = d.id
where b.altitude>c.altitude and c.altitude>d.altitude

[@ravishmahajan9314]

Here is my solution but it took me 30mins to solve it myself without looking at your solution, but not sure if i will be able to complete it while in interview exam 😔 But very happy that i completed it myself🙂
with cte as (
select t.hut1, t.hut2, h1.altitude hut1_alt , h2.altitude hut2_alt from trails t join mountain_huts h1 on
t.hut1 = h1.id join mountain_huts h2 on t.hut2 = h2.id
),
cte2 as (
select
CASE WHEN hut1_alt > hut2_alt THEN hut1 ELSE hut2 END h1,
CASE WHEN hut1_alt > hut2_alt THEN hut1_alt ELSE hut2_alt END h1_alt,
CASE WHEN hut1_alt < hut2_alt THEN hut1 ELSE hut2 END h2,
CASE WHEN hut1_alt < hut2_alt THEN hut1_alt ELSE hut2_alt END h2_alt
from cte
),
cte3 as (
select a.h1 as start, a.h2 middle , b.h2 end from cte2 a join cte2 b on
a.h2 = b.h1
)
select m1.name, m2.name, m3.name from cte3 join mountain_huts m1 on cte3.start = m1.id join mountain_huts m2 on cte3.middle= m2.id join mountain_huts m3 on
cte3.end = m3.id;

[@HimanshuSingh-cf7wr]

MY solution:-
select re.Name as startpt,ru.Name as middlept,rq.name as endpt
from
(select oo.higher,oo.lower,uo.lower as lowest,greatest(oo.hut1_alt,oo.hut2_alt) as higher_alt,
least(oo.hut1_alt,oo.hut2_alt) as lower_alt,least(uo.hut1_alt,uo.hut2_alt)as lowest_alt
from
(select case when hut1_alt>hut2_alt then hut1 else hut2 end as higher,
case when hut1_althut2_alt then hut1 else hut2 end as higher,
case when hut1_alt

[@iswillia123]

I solved it with a union this way
with t as (
select hut1, hut2 from trails
union
select hut2, hut1 from trails
),
p as (
select h1.name h1,
h2.name h2
from t,
mountain_huts h1,
mountain_huts h2
where h1.id = t.hut1
and h2.id = t.hut2
and h1.altitude > h2.altitude)
select p1.h1, p1.h2, p2.h2
from p p1,
p p2
where p1.h2 = p2.h1
order by 1,2
;

[@ashishdhodria9943]

with cte as (

select hut1, hut2 from trails t
union
select hut2, hut1 from trails t
)
select mh1.name, mh2.name, mh3.name from cte t1
inner join cte t2 on t1.hut2 = t2.hut1 and t1.hut1 t2.hut2
inner join mountain_huts mh1 on t1.hut1 = mh1.id
inner join mountain_huts mh2 on t1.hut2 = mh2.id
inner join mountain_huts mh3 on t2.hut2 = mh3.id
where mh1.altitude > mh2.altitude and mh2.altitude > mh3.altitude

[@akashb59]

with cte as (
select hut1, hut2, id, name, altitude from mountain_huts m join trails t on t.hut1 = m.id
union
select hut2, hut1, id, name, altitude from mountain_huts m join trails t on t.hut2 = m.id
)
select distinct c1.name,c2.name,c3.name from cte c1
join cte c2 on c1.altitude>c2.altitude and c1.hut2 = c2.hut1
join cte c3 on c2.altitude>c3.altitude and c2.hut2 = c3.hut1
where c2.name is not null and c3.name is not null

[@Alexpudow]

Hi, everyone. Thanks a lot techTF, good video!
My approach
with a as (
select t.hut1, t.hut2
from trails t
union all
select hut2, hut1 from trails
),
b as (
select t.hut1, t.hut2, t2.hut2 hut3
from a t left join a t2 on t.hut2=t2.hut1 and t2.hut2t.hut1
)
select m.name, m2.name, m3.name
from b join mountain_huts m on hut1=m.id
join mountain_huts m2 on hut2=m2.id and m.altitude>m2.altitude
join mountain_huts m3 on hut3=m3.id and m2.altitude>m3.altitude

[@mohanbagan5474]

Short and crisp✨

[@MrManda007]

SELECT DISTINCT start.name AS StartHut, middle.name AS MiddleHut, end.name AS EndHut
FROM mountain_huts start
INNER JOIN trails ON (start.id = trails.hut1 AND start.id < trails.hut2) OR (start.id = trails.hut2 AND start.id < trails.hut1)
INNER JOIN mountain_huts middle ON middle.id = CASE WHEN start.id = trails.hut1 THEN trails.hut2 ELSE trails.hut1 END
INNER JOIN trails t2 ON (middle.id = t2.hut1 AND middle.id < t2.hut2) OR (middle.id = t2.hut2 AND middle.id < t2.hut1)
INNER JOIN mountain_huts end ON end.id = CASE WHEN middle.id = t2.hut1 THEN t2.hut2 ELSE t2.hut1 END
WHERE start.altitude > middle.altitude AND middle.altitude > end.altitude
AND start.id end.id
ORDER BY StartHut, MiddleHut, EndHut;

[@ManthanShettigar-w2h]

with hut12 as (select hut1.name as hut1 , hut1.altitude , hut2.name as hut2 , hut2.altitude from trails
left join mountain_huts as hut1 on trails.hut1 = hut1.id
left join mountain_huts as hut2 on trails.hut2 = hut2.id
where hut1.altitude >= hut2.altitude ) ,
hut21 as (select hut2.name as hut2 , hut1.altitude , hut1.name as hut1 , hut1.altitude from trails
left join mountain_huts as hut1 on trails.hut1 = hut1.id
left join mountain_huts as hut2 on trails.hut2 = hut2.id
where hut2.altitude >= hut1.altitude )
select t1.hut1 as startpt , t1.hut2 as midpt , t2.hut2 as endpt from hut12 t1
join hut12 t2 on t1.hut2 = t2.hut1
UNION
select hut12.hut1 as startpt , hut12.hut2 as midpt , hut21.hut1 as endpt from hut12
join hut21 on hut12.hut2 = hut21.hut2
union
select hut21.hut2 as startpt , hut21.hut1 as midpt , hut12.hut2 as endpt from hut21
join hut12 on hut21.hut1 = hut12.hut1

[@OmkarPatil-r9r]

with cte1 as (
select
case when m.altitude > m1.altitude then t.hut1 else t.hut2 end as hut1 , case when m.altitude > m1.altitude then t.hut2 else t.hut1 end as hut2
from trails t
left join mountain_huts m
on t.hut1 = m.id
left join mountain_huts m1
on t.hut2 = m1.id)
select m1.name,m2.name,m3.name from cte1 c1
inner join cte1 c2
on c1.hut2 = c2.hut1
left join mountain_huts m1
on m1.id = c1.hut1
left join mountain_huts m2
on m2.id = c2.hut1
left join mountain_huts m3
on m3.id = c2.hut2
order by c1.hut1