Case Study: Analyze International Debt Statistics

Context

This project analyzes real world national and regional debt statistics published by The World Bank for several countries around the world across years 1970 to 2015. The dataset for analysis consists of information about debt (in USD) owed by several developing countries.

Dataset

international_debt_dataset

Questions

What is the total amount of debt that is owed by the countries listed in the dataset?
Which country owns the maximum amount of debt and what does that amount look like?
What is the average amount of debt owed by countries across different indicators?

Project Tasks

Examine the dataset
Find the number of distinct countries
Identify the distinct debt indicator
Determine the total sum of debt owed by countries in the data
Identify the country with the highest amount of debt
Determine the mean of debt across all countries
Identify the highest amount of principal repayments
Identify the mode for common debt indicator

%%sql
postgresql:///international_debt
SELECT *
FROM international_debt
LIMIT 10;

Step 1: Examine the dataset

%%sql
SELECT COUNT(DISTINCT country_name) AS total_distinct_countries
FROM international_debt;

Step 2: Find the number of distinct countries

%%sql 
SELECT DISTINCT indicator_code AS distinct_debt_indicators
FROM international_debt
ORDER BY distinct_debt_indicators ASC;

Step 3: Identify distinct debt indicators

%%sql
SELECT ROUND((SUM(debt) / 1000000),2) AS total_debt
FROM international_debt;

Step 4: Determine the total sum of debt owed by countries in the data

%%sql 
SELECT country_name, SUM(debt) AS total_debt
FROM international_debt
GROUP BY country_name
ORDER BY total_debt DESC
LIMIT 1;

Step 5: Identify the country with the highest amount of debt

%%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;

Step 6: Determine the mean of debt across all countries

%%sql
SELECT 
    country_name, 
    indicator_name
FROM international_debt
WHERE debt = (SELECT 
                  MAX(debt)
              FROM international_debt
              WHERE indicator_code = 'DT.AMT.DLXF.CD');

Step 7: Identify the highest amount of principal repayments

%%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;

Step 8: Identify the mode for common debt indicator