SQL Analysis of World Bank's Battle Against Poverty
When I came across World Bank and its mission “to end extreme poverty and boost prosperity on a livable planet”, I wanted to know more…
This is an organization that is committed to providing financial and technical assistance to developing countries all over the world. And their primary goals center on ending worldwide poverty in this decade.
AND, World Bank makes their data publicly available. So I couldn’t resist digging in. I’ve been learning SQL over the past couple months and this seemed like the perfect chance to use it. This is a BIG data set — just the thing for SQL.
I was curious…
🤔 What can this data tell me about World Bank’s mission?
🤔 How much money is involved?
🤔 Which parts of the world do they help?
First I needed to wrap my head around the data.
So I wrote a SQL query that would let me take a look at all the columns in this table.
The table has 30 columns with information including the name of the country, region, how much they’ve borrowed, how much they currently owe, the service charge they’re paying, their credit status, and more.
I wanted to see how many records this table contains. So I wrote this SQL query:
It returned a result of 1,109,994 rows.
I decided to see the total amount owed to The International Development Association (IDA) — the part of the World Bank that helps the world's poorest countries.
They’re owed 20,164,338,829,274.426
I wanted to get a feel for the amounts being borrowed in individual transactions, so I wrote the following SQL query:
I chose to limit the results because this is a huge data set and I was only trying to get a snapshot. Adding a record limit got me the results much faster. At a glance, I could see that the amounts ranged from $0 to over $2 million.
I already knew the minimum owed was $0, but I was curious about the maximum. So I wrote the following SQL query:
The maximum owed is $793,256,127.64.
Then I wanted to see if individual countries could have more than one loan. So I picked a country and wrote the following SQL query to learn more.
The answer is a resounding yes. Nicaragua has 13,704 records in this database. I wondered how many of those loans had been repaid so I wrote the following SQL query:
Nicaragua has repaid 4223 of their loans.
It was becoming clear that World Bank is making a big impact. But how many countries are they helping?
Turns out World Bank is working with 137 countries. I noticed that the number of records for each country varied quite a bit. So I decided to find out the 5 countries World Bank has worked with the most. So I wrote this SQL query:
And it yielded the following results:
I was curious about the correlation between the number of transactions and the amount borrowed. So I wrote the following SQL query to see if they matched:
So not much overlap in the top 5.
The countries are also grouped by region. So to figure out how many regions were represented in the data, I wrote this SQL query:
There were 13 regions.
Well of course I became curious about how the regions are divided, so I decided to find the region names. I wrote the following SQL query:
I noticed an issue in the results. Because 2 of the regions were written both in all caps and with just the first letter capitalized, they were showing up twice. Fortunately I had just read about how to convert the column data to all caps in Walter Shield’s SQL QuickStart Guide book this morning. So I cleaned it up with this query:
Then I wanted to see how much each region currently owes. I was especially curious about the amount the top region owed. So I wrote another query and made sure the highest number was at the top:
I wanted to double check that using the command ‘UPPER’ had worked the way I thought. I expected it would combine the two instances of each region. But I wanted to make sure.
So I ran the query without using it and then added the totals of the duplicates together to make sure they matched the results in my previous query. And they did! Feeling like a SQL rockstar here.
Now I have a better idea of the amounts of the loans and where the money is going. But what is the money being used for? When I first examined the data, I noticed a column named “Projects”. So I decided to see what types of projects are being funded.
At a glance I saw projects including highways, irrigation, education, railway, and many more. So I ran a count.
There are 7885 separate project names.
Next I started wondering about the service fees. So I wrote a SQL query to find out the average service charge rate for a loan.
The average service charge comes out to 0.778.
Then I wondered about the lowest and highest service charge rates. So I wrote this SQL query:
And it yielded the following results:
Wrap Up
Up until last week, I didn’t know World Bank even existed, let alone that they’ve embarked on the ambitious mission to end poverty.
And thanks to SQL, I learned a lot about this organization and the work they do.
TLDR: The High Points
👉The total amount owed to The International Development Association (IDA) — the part of the World Bank that helps the world's poorest countries: 20,164,338,829,274.426
👉The maximum amount owed by a single country is $793,256,127.64.
👉To get a snapshot I checked Nicaragua. They have 13,704 records in this database and have repaid 4223 of their loans.
👉World Bank is working with 137 countries. The 5 countries World Bank has worked with the most are India, Bangladesh, Pakistan, Tanzania, and Ghana.
👉The countries are divided into the following regions:
South Asia
Africa
East Asia and Pacific
Europe and Central Asia
Africa East
Africa West
Middle East an North Africa
Latin America and Caribbean
Eastern and Southern Africa
Western and Central Africa
Other
👉Projects include highways, irrigation, education, railway, and many more. I ran a count and discovered that there are 7885 separate project names.
👉 The service charge rates range from 0-4.95, with an average rate of 0.778.
Let’s Connect
If you’ve read this far, we’re practically already friends. So if you’d like to chat about data, ask me a question, or have an opportunity you think I'd be a fit for, send me a message on LinkedIn.
If you’d like to see an education-related data analytics project I did in Tableau, check out this one.
