Rivers by Country

/*Problem 9.    Rivers by Country
For each country in the database, display the number of rivers passing through
that country and the total length of these rivers. When a country does not have
any river, display 0 as rivers count and as total length. Sort the results by
rivers count (from largest to smallest), then by total length (from largest
to smallest), then by country alphabetically.*/

--MY SOLUTION:

SELECT
    C.CountryName,
    CN.ContinentName,
    COUNT(R.RiverName) AS [RiversCount],
    ISNULL(SUM(R.LENGTH), 0) AS [TotalLength]
FROM Rivers R
JOIN CountriesRivers CR
ON CR.RiverId = R.Id
RIGHT OUTER JOIN Countries C
ON C.CountryCode = CR.CountryCode
JOIN Continents CN
ON CN.ContinentCode = C.ContinentCode
GROUP BY C.CountryName, CN.ContinentName
ORDER BY RiversCount DESC, TotalLength DESC, C.CountryName

--AUTHOR SOLUTION

SELECT
  c.CountryName, ct.ContinentName,
  COUNT(r.RiverName) AS RiversCount,
  ISNULL(SUM(r.LENGTH), 0) AS TotalLength
FROM
  Countries c
  LEFT JOIN Continents ct ON ct.ContinentCode = c.ContinentCode
  LEFT JOIN CountriesRivers cr ON c.CountryCode = cr.CountryCode
  LEFT JOIN Rivers r ON r.Id = cr.RiverId
GROUP BY c.CountryName, ct.ContinentName
ORDER BY RiversCount DESC, TotalLength DESC, CountryName