SELECT 
  crime_t.city, 
  crime_t.safety_index, 
  sunshine_t.year / sunshine_max.year * 100 AS sunshine_index, 
  crime_t.safety_index + sunshine_index AS safe_and_sunny_index,
  RANK() OVER (ORDER BY safe_and_sunny_index DESC) AS rank
FROM 
  @kaggle.ahmadjalalmasood123_world_crime_index.world_crime_index crime_t,
  @kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city sunshine_t,
  (SELECT MAX("year") AS "year" FROM @kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city) sunshine_max
WHERE 
    crime_t.city = (sunshine_t.city || ', ' || sunshine_t.country)
ORDER BY safe_and_sunny_index DESC