Project Description
A country may borrow money to manage its economy. Infrastructure spending, for example, is a costly endeavour required for a country's citizens to lead comfortable lives. The World Bank finances such investments.
In this project, we will analyse international debt data collected by The World Bank. The dataset contains information about the amount of debt (USD) owed by developing countries. We are going to answer questions like:
What is the total amount of debt owed by countries?
Which country owes the maximum amount of debt, and what does that amount look like?
What is the average debt owed by countries across different debt indicators?
The data is provided by The World Bank. It contains national and regional debt statistics for several countries across the globe as recorded from 1970 to 2015.
1. The World Bank's international debt data
Let's start by inspecting the columns from the international_debt table. Also, we'll limit the output to the first ten rows.
SQL:
SELECT
*
FROM
international_debt
LIMIT 10;
Tableau:
2. Find the number of distinct countries
From the first ten rows, we see the debt owed by Afghanistan in the different debt indicators. There are repetitions in the country names because a country is likely to have an obligation in more than one debt category. But we do not know the number of countries in the table. Without a count of unique countries, we will be unable to perform statistical analyses holistically.
In this section, we will extract the number of unique countries in the table.
SQL:
SELECT
COUNT(DISTINCT(country_name)) AS total_distinct_countries
FROM international_debt;
Tableau:
3. Find the number of distinct debt indicators
There are a total of 124 countries. As we saw in the first section, there is a column called indicator_name that specifies the purpose of the debt. Besides that column, another column called indicator_code symbolises the category of these debts. These debt indicators will help us understand the areas to which a country can possibly be indebted.
SQL:
SELECT
DISTINCT(indicator_code) AS distinct_debt_indicators
FROM international_debt
ORDER BY distinct_debt_indicators;
Tableau:
4. Total amount of debt owed by countries
As mentioned, the financial debt of a particular country represents its economic state. But if we were to project this on an overall global scale, how would we approach it? Let's switch gears from the debt indicators and find out the total amount of debt (USD) owed by different countries.
SQL:
SELECT
ROUND((SUM(debt) / 1000000), 2) AS total_debt
FROM international_debt;
Tableau:
5. Country with the highest debt
"Human beings cannot comprehend very large or very small numbers. It would be useful for us to acknowledge that fact." - Daniel Kahneman.
Now we have the total amount of debt owed by countries, let's find the country that owes the highest amount of debt. Note that this is the sum of different debts a country owes across several categories.
SQL:
SELECT
country_name,
sum(debt) AS total_debt
FROM international_debt
GROUP BY country_name
ORDER BY total_debt DESC
LIMIT 10;
Tableau:
6. Average amount of debt across indicators
So, it is China. We can dig further to determine how much debt a country owes? This will give us a better sense of the distribution of the amount of debt across different indicators.
SQL:
SELECT
indicator_code AS debt_indicator,
indicator_name,
avg(debt) AS average_debt
FROM international_debt
GROUP BY debt_indicator, indicator_name
ORDER BY average_debt DESC
LIMIT 10;
Tableau:
7. The highest amount of principal repayments
We see the indicator DT.AMT.DLXF.CD tops the chart for average debt. This category includes repayment of long-term debts. Countries take on long-term debt to acquire immediate capital. An interesting observation is that there is a significant difference in the amounts of the indicators after the second one. This indicates that the first two indicators might be the most severe categories.
We can investigate this a bit more to find out which country owes the highest debt in the category of long-term debts (DT.AMT.DLXF.CD). Since not all countries suffer from the same kind of economic disturbances, this finding will allow us to understand that country’s financial condition more specifically.
SQL:
SELECT
country_name,
indicator_name
FROM international_debt
WHERE debt = (SELECT
MAX(debt)
FROM international_debt
WHERE indicator_code='DT.AMT.DLXF.CD');
Tableau:
8. The most common debt indicator
China has the highest long-term debt (DT.AMT.DLXF.CD) category. We saw that long-term debt is the top category for the average amount of debt. But is it the most common indicator that the countries owe their debt?
SQL:
SELECT
indicator_code,
COUNT(indicator_code) AS indicator_count
FROM international_debt
GROUP BY indicator_code
ORDER BY indicator_count DESC, indicator_code DESC
LIMIT 20;
Tableau:
9. Other viable debt issues and conclusion
There are six debt indicators in which all the countries listed in our dataset have taken debt. The indicator DT.AMT.DLXF.CD is also there in the list. This gives us a clue that all these countries suffer from a common economic issue. Let's change tracks from debt_indicators and focus on the amount of debt again. Let's find out the maximum amount of debt that each country has. With this, we will be able to identify the other plausible economic issues.
SQL:
SELECT
country_name,
MAX(debt) AS maximum_debt
FROM international_debt
GROUP BY country_name
ORDER BY maximum_debt DESC
LIMIT 10;
Tableau:
In this notebook, we looked at the debt owed by countries across the globe. We extracted a few summary statistics from the data and unravelled interesting facts and figures.