International Debt Analysis with SQL

In this project the goal is to analyze international debt statistics sourced from Worldbank. As someone from a developing country International debt has always been of great interest to me. These raw figures provide insights into the impact of debt on economies and how countries manage their financial obligations.

Push the play/run button at the top right of the page to watch the data in motion.

Importing the Data from CSV

1. Loading the CSV File:

The first step is to load the data into a DataFrame for analysis.

2. Query:

-- select_external_data SELECT * FROM 'international_debt.csv';

Run to view results

3. Result:

The data has columns country_name, country_code, indicator_name, indicator_code, and debt. There are 2,357 rows in total.

Creating the Table

1. Creating a New Table:

We use CREATE TABLE to create a new table called international_debt to store the data. The table includes columns for country_name, country_code, indicator_name, indicator_code, and debt.

Then we create a composite PRIMARY KEY using both country_code and indicator_code since multiple rows may have the same country code but they all have different indicators. This ensures that all country and indicator combinations are unique.

2. Verifying Table Creation:

We then use the SHOW TABLES command to ensure the table international_debt was created

3. Query:

-- create_new_table CREATE TABLE international_debt ( country_name VARCHAR(56), country_code CHAR(3), indicator_name VARCHAR(100), indicator_code VARCHAR(15), debt FLOAT, PRIMARY KEY (country_code, indicator_code) ); SHOW TABLES;

Run to view results

4. Result:

The output of the SHOW TABLES command shows the international_debt table is created.

Inserting Data into the Table

1. Inserting Values Into The Table:

Here we use the INSERT INTO command to insert the data from the csv file in df_2 into the newly created 'international_debt' table

Usually the INSERT INTO and VALUES command would be used, but in this case we used the data from dataframe 2

2. Query:

-- insert_values_to_table INSERT INTO international_debt SELECT country_name, country_code, indicator_name, indicator_code, debt FROM df_2; SELECT * FROM international_debt;

Run to view results

3. Result:

The data from dataframe 2 has been successfully loaded. We can see the 5 matching columns and 2357 rows.

Counting the Number of Distinct Countries

1. Counting Unique Country Names:

To find the total number of distinct countries in the dataset, we use the COUNT and DISTINCT functions to only count unique country names. We alias it as total_distinct_countries with the AS function

2. Query:

-- num_distinct_countries SELECT COUNT(DISTINCT country_name) AS total_distinct_countries FROM international_debt;

Run to view results

3. Result:

There are 124 different countries in the database detailed by the total_distinct_countries column. The query is saved as num_distinct_countries.

Finding the country with the single highest total debt

1. Summing the Debt for Each Country:

First, we calculate the total debt for each country by adding the debt values for each country. We use the SUM(debt) function and aliased as total_debt with the AS function.

2. Grouping Data by Country:

Since we're working with SUM which is an aggregate function, we GROUP BY country_name so that the sum is calculated for each country individually.

3. Sorting by Total Debt:

Next, we use ORDER BY and DESC to sort the results by the total debt from highest to lowest.

4. Limiting Results to One Country:

Finally, we use LIMIT 1, which limits to the top result only, since we are only looking for 1 the rest of the data is irrelevant.

Query

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

Run to view results

5. Result:

The country with the highest total debt is China, with a total debt of $285,793,490,528

Finding the country with the lowest principal repayment

1. Selecting Principal Repayment Data:

First we SELECT both country_name and indicator_name, as we are looking for principal repayments on external debt.

2. Finding Minimum Debt Value:

Then we use the MIN(debt) function to find the lowest principal repayment and alias this AS lowest_repayment.

3. Filtering by Indicator Code:

We filter for rows WHERE indicator_code = 'DT.AMT.DLXF.CD', which only shows us principal repayments on external debt.

4. Grouping and Sorting Data:

Since this is an aggregate query, we GROUP BY both country_name and indicator_name. We then SORT BY lowest_repayment in ascending order (which if unspecified is ascending).

5. Limiting Results to One Country:

Finally we use LIMIT 1 which again limits the result to the top row only.

6. Query:

-- lowest_principal_repayment SELECT country_name, indicator_name, MIN(debt) AS lowest_repayment FROM international_debt WHERE indicator_code = 'DT.AMT.DLXF.CD' GROUP BY country_name, indicator_name ORDER BY lowest_repayment LIMIT 1;

Run to view results

7. Result:

Timor-Leste is the country with the lowest principal repayment at $825,000

In conclusion, this project provided a look into international debt statistics, highlighting the financial obligations of various countries. Thank you for reading!

About Me

My name is Joel Z and I am a Sales Engineer with a background in Networking, currently pursuing a degree in cybersecurity. My expertise is in developing network solutions while prioritizing data security. I excel at bridging the gap between technical and business needs. These projects are a way for me to share my journey with others, while hopefully sparking curiosity in those who want to learn alongside me.