https://www.youtube.com/watch?v=rM1BVoBke04&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=2

drop table if exists mountain_huts;
create table mountain_huts 
(
	id 			integer not null unique,
	name 		varchar(40) not null unique,
	altitude 	integer not null
);
insert into mountain_huts values (1, 'Dakonat', 1900);
insert into mountain_huts values (2, 'Natisa', 2100);
insert into mountain_huts values (3, 'Gajantut', 1600);
insert into mountain_huts values (4, 'Rifat', 782);
insert into mountain_huts values (5, 'Tupur', 1370);

drop table if exists trails;
create table trails 
(
	hut1 		integer not null,
	hut2 		integer not null
);
insert into trails values (1, 3);
insert into trails values (3, 2);
insert into trails values (3, 5);
insert into trails values (4, 5);
insert into trails values (1, 5);

select * from mountain_huts;
select * from trails;

WITH
CASE WHEN
FLAG

WITH CTE AS (
SELECT t.hut1,h1.name,h1.altitude,t.hut2 FROM trails t
JOIN mountain_huts h1 ON t.hut1 = h1.id),

CTE2 AS (
SELECT CTE.hut1 AS Start_Hut,CTE.name AS Start_name,CTE.altitude AS St_Alt,CTE.hut2 AS End_Hut,h2.name AS End_name,h2.altitude AS End_Alt,
CASE WHEN CTE.altitude > h2.altitude THEN 1 ELSE 0 END AS Alt_Flag
FROM CTE JOIN mountain_huts h2 ON CTE.hut2 = h2.id
),

CTE_Final AS (
SELECT
CASE WHEN Alt_Flag = 1 THEN Start_Hut ELSE End_Hut END AS Start_Hut,
CASE WHEN Alt_Flag = 1 THEN Start_Name ELSE End_Name END AS Start_Name,
CASE WHEN Alt_Flag = 1 THEN End_Hut ELSE Start_Hut END AS End_Hut,
CASE WHEN Alt_Flag = 1 THEN End_Name ELSE Start_name END AS End_Name
FROM CTE2 
)

SELECT f1.Start_Name AS startpt,
f1.End_Name AS middlept,
f2.End_Name AS endpt
FROM CTE_Final f1 JOIN CTE_Final f2 ON f1.end_hut = f2.start_hut;