https://projects.datacamp.com/projects/754 - Mistake in Task 9!

This is the solution from DataCamp

SELECT
	country_name,
	indicator_code,
	MAX(debt) AS maximum_debt
FROM international_debt
GROUP BY country_name, indicator_code
ORDER BY maximum_debt DESC
LIMIT 10

The solution provides several (!) debt indicators for each country. So we have China in 1st and 3rd row, etc. You can omit "max()" and "group by" and you will get the same result. It is because indicator_code values are unique for each country, so GROUP BY will return the same result as it will be without it. So the solution simply orders all debts in DESC order like this code:

SELECT
	country_name,
	indicator_code,
	debt AS maximum_debt
FROM international_debt
ORDER BY maximum_debt DESC
LIMIT 10

But the task is to "Find out the debt indicators for which a country owes its highest debt." So this means - find one (!) debt indicator with highest debt amount for each country.

Unfortunately, we do not have keys, but this code will more or less work:

SELECT 
	country_name,
	indicator_code,
	cnmax.maximum_debt
	FROM international_debt i
	INNER JOIN
	(SELECT
		MAX(debt) AS maximum_debt
		FROM international_debt
		GROUP BY country_name) as cnmax
	ON i.debt = cnmax.maximum_debt
ORDER BY maximum_debt DESC
LIMIT 10