SELECT 
  passport_color, 
  SUM(deaths.cumulative_deaths_in_armed_conflicts) deaths, 
  SUM(population.population_2022) population, 
  AVG(deaths.cumulative_deaths_in_armed_conflicts / population.population_2022) * 1000 deaths_per_1000
FROM
  @kaggle.willianoliveiragibin_millions_have_died.cumulative_deaths_in_armed_conflicts_new deaths
INNER JOIN @jsoares.passport_colours.passports passports 
  ON deaths.country = passports.country
INNER JOIN @kaggle.ahmadjalalmasood123_world_population_dataset.world_population_2022 population 
  ON deaths.country = population.country_or_dependency
GROUP BY passport_color
ORDER BY 4 DESC