SELECT
    t.Id,
    t.Email,
    t.CountryCode,
    t.Trips
FROM (SELECT
    a.Id,
    a.Email,
    c.CountryCode,
    COUNT(*) AS Trips,
    DENSE_RANK() OVER(PARTITION BY c.CountryCode ORDER BY COUNT(*) DESC, a.Id) AS [Rank]
FROM Accounts AS a
JOIN AccountsTrips AS at ON at.AccountId = a.Id
JOIN Trips AS t ON t.Id = at.TripId
JOIN Rooms AS r ON r.Id = t.RoomId
JOIN Hotels AS h ON h.Id = r.HotelId
JOIN Cities AS c ON c.Id = h.CityId
GROUP BY c.CountryCode, a.Email, a.Id) AS t
WHERE t.Rank =1
ORDER BY Trips DESC, t.Id