Highest Peak and Longest River by Country - with CTE ( T-SQL )

--17.Highest Peak and Longest River by Country from Subqueries and Joins
USE Geography
WITH CTE_PEAKS (CountryCode, CountryName, MountainRange, PeakName, Elevation, RANK_PEAKS) AS
     (
      SELECT Countries.CountryCode, Countries.CountryName, Mountains.MountainRange, Peaks.PeakName, Peaks.Elevation,
      DENSE_RANK() OVER(PARTITION BY Countries.CountryName ORDER BY Peaks.Elevation DESC) AS RANK_PEAKS
      FROM Countries
      LEFT JOIN MountainsCountries ON MountainsCountries.CountryCode = Countries.CountryCode
      LEFT JOIN Mountains ON Mountains.Id = MountainsCountries.MountainId
      LEFT JOIN Peaks ON Peaks.MountainId = Mountains.Id
     ),
     CTE_RIVERS (CountryCode, RiverName, RiverLength, RANK_RIVERS) AS
     (
      SELECT Countries.CountryCode, Rivers.RiverName, Rivers.Length AS  RiverLength,
      DENSE_RANK() OVER(PARTITION BY Countries.CountryName ORDER BY Rivers.Length DESC) AS RANK_RIVERS
      FROM Countries
      LEFT JOIN CountriesRivers ON CountriesRivers.CountryCode = Countries.CountryCode
      LEFT JOIN Rivers ON Rivers.ID = CountriesRivers.RiverId
     )
SELECT TOP(5) CountryName, Elevation AS HighestPeakElevation, RiverLength AS LongestRiverLength
FROM CTE_PEAKS
LEFT JOIN  CTE_RIVERS ON CTE_RIVERS.CountryCode = CTE_PEAKS.CountryCode
WHERE RANK_RIVERS = 1 AND RANK_PEAKS = 1
ORDER BY Elevation DESC, RiverLength DESC, CountryName;