SQL: World Bank Loans

This Dataset is from the International Development Association, part of the World Bank Group. The data analyzed in this project was last updated at the end of November 2024. You can find the original data and helpful information from the IDA here.

1) To help me explore and make sense of the data I returned the entire table to see all column names and used this query as a reference for what kind of queries I wanted to write next.

SQL World Bank Result Set 1

2) After looking through the columns I was curious to see how big this table was so I returned the count of all rows and found out that the table was about 1.3 million rows.

SQL World Bank Result Set 2

3) I figured it would be useful to have a very brief snapshot of what a single country still owed the IDA so I wrote a query that will return the number of transactions still owed as well as the total dollar amount still owed by a particular country, in this case Nicaragua. If I, or someone else looking through my queries, wanted to know this information for another country the only thing that would need to change is the last word of the query to a different country.

SQL World Bank Result Set 3

4) Then I got curious. What was the largest single loan still owed to the World Bank?

SQL World Bank Result Set 4

5) Of course that begs the question how much money has the World Bank loaned to countries that is currently outstanding?

SQL World Bank Result Set 5

6) To get a better idea of how all countries compare to each other based on their outstanding balances due to the World Bank I grouped the country, number of transactions, and total amount of money still owed to the IDA.

SQL World Bank Result Set 6

7) Then I ordered the query above by money owed to the IDA to show which countries owe the most money to the World Bank.

SQL World Bank Result Set 7

8) I wanted to keep following this trend of how much countries still owe to the IDA. Initially I tried to select the Country, Project Name, Project ID, and Amount Due to IDA and just order the return in desc order by Amount Due to IDA to show the 100 most expensive projects the World Bank is funding through the IDA. The result from this query showed 2 things I didn’t like.

One thing I didn’t want to see was the two projects with null values in the Due to IDA column. The second thing I didn’t like in this return was that it did not show me the 100 most expensive projects, it showed me only a couple projects several times, you can tell because many of the Project IDs are the same.

SQL World Bank Result Set 8

9) At this point in my data career I’m still a novice at cleaning data so I just filtered out these projects with a WHERE NOT filter.

SQL World Bank Result Set 9

10) The second issue was a little bit tougher to fix. I’m embarrassed to say that this problem stumped me for a while. Eventually I figured out that this issue was caused by the End of Period column, or more accurately, my poor understanding of the End of Period column. I wrote all of my queries with the DBeaver application in SQLite and, apparently, DBeaver labeled the End of Period column as a string (text) data type instead of a date data type.

In this query below I added the End of Period column to better illustrate the problem. You can see that many of the rows are the same project, but these projects have been assessed several times through 2011 – 2024.

SQL World Bank Result Set 10

11) Unbeknownst to me at the time, the type of data in a column is very consequential for your queries. However, my new nemesis (the End of Period column) was no match for my secret weapon: furious and frantic googling. I added a new column to my banking_data table called End of Period Date which converts all the information from the End of Period column and puts it into a Date format that SQLite could recognize. Of course after I corrected my mistake I had to update all of my previous queries which is why you see the End of Period Date column in those.

12) Verifying that the End of Period Date Column was added to the banking_data table successfully.

SQL World Bank Result Set 12

13) Eventually I prevailed and found the 100 most expensive projects the World Bank has funded through the IDA.

SQL World Bank Result Set 13

14) After thinking about this roadblock for a while I wondered if I could arrive at the same result using a WHERE filter instead of just ordering the End of Period column first. You can see the query below returns the same result, although I think using the WHERE filter is a little more foolproof method of finding the most expensive projects the IDA has funded.

SQL World Bank Result Set 14

15) Next I wanted to explore the service charge rate for different projects as well as the credit statuses of projects funded by the IDA. The query below just returns the average service charge rate for all projects in this dataset, which would include assessments of projects from 2011 – 2024.

SQL World Bank Result Set 15

16) And this query returns the average service charge rate for all projects assessed at the end of Nov 2024.

SQL World Bank Result Set 16

17) I thought it also might be helpful to know how the service charge rate has changed over time. The query below groups the average service charge rate by the date of project assessment, which is the End of Period column.

SQL World Bank Result Set 17

18) And this query shows the average service charge rate of all projects belonging to a single country.

SQL World Bank Result Set 18

19) I also wrote a query to return all projects from a single country that have a service charge rate higher than the average service charge rate across projects from all countries.

SQL World Bank Result Set 19

20) The last query I wrote related to the service charge rate is below, this one shows the countries with the highest average service charge rate for all their projects with the World Bank.

SQL World Bank Result Set 20

21) Next I wanted to explore the credit statuses of these projects, the query below returns the number of projects currently in different credit statuses.

SQL World Bank Result Set 21

22) And this query groups those projects by country.

SQL World Bank Result Set 22

23) And in this query I made the WHERE filter a little tighter to identify the credit statuses of projects from just one country.

SQL World Bank Result Set 23

Thank you for making it to the end of my World Bank analysis using SQL! If you haven’t already, connect with me on LinkedIn.