World Bank Loan Data Analysis Using SQL

About this project

THE DATA

The data comes from The World Bank Group. This data set contains more than a million records with 30 columns. In this Project I am going to use the SQL (Structured Query Language) as a tool to analyze the dataset.

The International Development Association (IDA) credits are public and publicly guaranteed debt extended by the World Bank Group. IDA provides development credits, grants and guarantees to its recipient member countries to help meet their development needs. Credits from IDA are at concessional rates. Data are in U.S. dollars calculated using historical rates. This dataset contains historical snapshots of the IDA Statement of Credits and Grants including the latest available snapshot. The World Bank complies with all sanctions applicable to World Bank transactions.

I have overviewed the the world data then focused my analysis more on India. Main Business Question which I have explored are below:

1. How many Countries have taken Loan From World Bank.

2. Top 5 Countries with highest Loan amount.

3. Top 5 Countries with highest Due amount.

4. Top 5 Countries who have taken most loan amount in percentage.

5. Project First Approved Loan for India.

6. Top 5 Project in India having Highest Loan.

7. Top 5 project in India having highest loan and the Due amount of those loans.

8. Total number of loan projects that have fully repaid the loan.

9. Top 5 Maximum approval time in project for India.

10. Top 5 Minimum approval time in project for India.

Now let's Analyze each business Questions asked above.

1. How many Countries have taken Loan From World Bank.

   SELECT
           COUNT(DISTINCT country)
   FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"

So After running the above Query There are total 137 Countries Which has taken the Loan From World Bank.

2. Top 5 Countries with highest Loan amount.

  SELECT
         country,
         CONCAT('$',ROUND(SUM(REPLACE("Original Principal Amount",',','')::numeric)/1000000000,2) -
         ROUND(SUM(REPLACE("Cancelled Amount",',','')::numeric)/1000000000,2)
         ,'B')"All Loan Amount"
 FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
        GROUP BY 1
 order by SUM(REPLACE("Original Principal Amount",',','')::numeric)-SUM(REPLACE("Cancelled 
 Amount",',','')::numeric desc 
        LIMIT 5

India is having the Highest Loan amount($6934 B) then Bangladesh, Pakistan, Vietnam, Ethiopia

3. Top 5 Countries with highest Due amount

SELECT 
          country,
           CONCAT('$',ROUND(SUM(REPLACE("Due to IDA",',','')::numeric)/1000000000,2),'B') as "Due Loan 
           Amount"
FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
           GROUP BY 1
order by SUM(REPLACE("Due to IDA",',','')::numeric) desc 
           LIMIT 5

Here INDIA has taken about ($3385.50B) of the Due Loan and Bangladesh, Pakistan, Vietnam and Nigeria

4. Top 5 Countries who have taken most loan amount in percentage

 WITH total_loan_per_country AS(
   SELECT 
          country,
          ROUND(SUM(REPLACE("Original Principal Amount",',','')::numeric)-
              SUM(REPLACE("Cancelled Amount",',','')::numeric)
             /1000000000,2) as "All Loan Amount"
  FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
       GROUP BY 1
 ORDER BY SUM(REPLACE("Original Principal Amount",',','')::numeric)-
        SUM(REPLACE("Cancelled Amount",',','')::numeric) desc 
)SELECT 
     country,
    CONCAT(ROUND(("All Loan Amount"/SUM("All Loan Amount") OVER())*100.0,2),'%') as total_loan_perc 
FROM total_loan_per_country
     GROUP BY 1,"All Loan Amount" ORDER BY ("All Loan Amount"/ SUM("All Loan Amount") OVER()) desc Limit 5

Here INDIA has taken about 14% of the total Loan and Bangladesh(7%), Pakistan(6%), Vietnam(5%) and Ethiopia(5%)

5. Project First Approved Loan for India.

SELECT
       "Project Name",
       "Board Approval Date"
FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
     WHERE country ='India'
LIMIT 1

The First Loan which has been approved by World Bank to India was on 20-Jun-1961 which was for the Project named HIGHWAYS

6. Top 5 Project in India having Highest Loan.

SELECT 
     "Project Name",
     CONCAT('$',ROUND(SUM(REPLACE("Original Principal Amount",',','')::numeric)/1000000000,2) -
    ROUND(SUM(REPLACE("Cancelled Amount",',','')::numeric)/1000000000,2)
    ,'B') as "Total Loan Amount" FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data" 
   WHERE country='India' GROUP BY 1
   order by SUM(REPLACE("Original Principal Amount",',','')::float) desc LIMIT 5

India has taken Highest Loan amount for Elementary Education($380.70 B) then PMGSY Rural Roads Project($259.64 B)

7. Top 5 project in India having highest loan and the Due amount of those loans.

  SELECT
    "Project Name",
    CONCAT('$',ROUND(SUM(REPLACE("Original Principal Amount",',','')::numeric)/1000000000,2) -
    ROUND(SUM(REPLACE("Cancelled Amount",',','')::numeric)/1000000000,2)
    ,'B') as "Total Loan Amount",
   CONCAT('$',ROUND(SUM(REPLACE("Due to IDA",',','')::numeric)/1000000000,2),'B') as "Due Loan 
   Amount"
  FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
 WHERE country='India'
 GROUP BY 1
 order by SUM(REPLACE("Original Principal Amount",',','')::numeric)-
 SUM(REPLACE("Cancelled Amount",',','')::numeric) desc
 LIMIT 5

India has taken Highest Loan amount for Elementary Education($292.30 B) then PMGSY Rural Roads Project($149.67 B)

8. Total number of loan projects that have fully repaid the loan.

  WITH CTE AS(
        SELECT 
                  "Project Name",
                  CONCAT('$',ROUND(SUM(REPLACE("Original Principal Amount",',','')::numeric)/1000000000,2)
                  -ROUND(SUM(REPLACE("Cancelled Amount",',','')::numeric)/1000000000,2),'B') as "Total Loan  
                  Amount"
 FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data" 
                WHERE country='India' and "Credit Status"  IN('Fully Repaid')
       GROUP BY 1
               order by SUM(REPLACE("Original Principal Amount",',','')::numeric) - 
               SUM(REPLACE("Cancelled Amount",',','')::numeric) desc
        )
  SELECT
        COUNT(*) as "total Count of paid Loan"
  FROM CTE

So there are total 42 Projects for which India has taken the loan and Fully Repaid those Loans.

9. Top 5 Maximum approval time in project for India

SELECT 
        DISTINCT "Project Name",
        ROUND(("Closed Date (Most Recent)"::date - 
        "Board Approval Date"::date)/365.25,2) as "Loan Process Time(years)"
 FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
       WHERE country ='India' 
 ORDER BY 2 desc

UPPER INDRAVATI POWE was the Project for which the Processing time is the longest having approx 12 years.

10. Top 5 Minimum approval time in project for India

SELECT 
        DISTINCT "Project Name",
        "Closed Date (Most Recent)"::date - 
        "Board Approval Date"::date as "Loan Process Time(days)"
FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data"
        WHERE country ='India' 
ORDER BY 2 
        LIMIT 5

MOZORAM ROADS Project has been the project which has been quickest in approval which was 71 days.

INSIGHTS

After looking at the data, I noticed that money being lent out to INDIA is the major Country which takes the 14% of Overall 137 countries Loan given By the World Bank. All the Loans are given to Borrower Government Department named Department of Economic Affair "Controller of Aid Accounts and Audit" for INDIA. The best insight I found is INDIA fully paid all the loan in 39 years and 6 month from date of first date of repayment.