126

WITH passCnt(ID_psg, cnt)
AS
(
	SELECT ID_psg, COUNT(*) AS cnt
	FROM Pass_in_trip
	GROUP BY ID_psg
),
maxPass(ID_psg)
AS
(
	SELECT ID_psg
	FROM passCnt
	WHERE cnt =
	(
		SELECT MAX(cnt)
		FROM passCnt
	)
),
lastPass(ID_psg)
AS
(
	SELECT MAX(ID_psg)
	FROM Pass_in_trip
),
firstPass(ID_psg)
AS
(
	SELECT MIN(ID_psg)
	FROM Pass_in_trip
),
lagLeadPass(ID_psg)
AS
(
	SELECT ID_psg,
		CASE
			WHEN ID_psg =
			(
				SELECT *
				FROM firstPass(ID_psg)
			)
			THEN
			(
				SELECT *
				FROM lastPass(ID_psg)
			) AS prev
			ELSE
				LAG(ID_psg) OVER(ORDER BY ID_psg) AS prev
		END,
		CASE
			WHEN ID_psg =
			(
				SELECT *
				FROM lastPass(ID_psg)
			)
			THEN
			(
				SELECT *
				FROM firstPass(ID_psg)
			) AS next
			ELSE
				LEAD(ID_psg) OVER(ORDER BY ID_psg) AS next
		END
	FROM
	(
		SELECT DISTINCT ID_psg
		FROM Pass_in_trip
	) t
)
SELECT *
FROM lagLeadPass