Built with datascienceportfol.io

Bank Loan Analysis

Project photo

Skills

About this project

USE Others
 
==================================================================BANK LOAN ANALYSIS==================================================================================================
 
---Total Number of Applications
Select COUNT(id) as Total_Number_of_Application 
from bank_loan
 
 
---MTD Loan Applications
Select  COUNT(id) as Total_Number_of_Application 
from bank_loan
where MONTH(issue_date) = 12 
  AND YEAR(issue_date) = 2021
 
 
 
---PMTD Loan Applications
Select  COUNT(id) as PMTD_Number_of_Application 
from bank_loan
where MONTH(issue_date) = 11 
  AND YEAR(issue_date) = 2021
 
 
---Total Funded Amount
Select SUM(loan_amount) as Total_funded_Amount
from bank_loan
 
 
---MTD Total Funded Amount
Select SUM(loan_amount) as MTD_funded_Amount
from bank_loan
where MONTH(issue_date) =  12 
  AND YEAR(issue_date) = 2021
 
 
---PMTD Total Funded Amount
Select SUM(loan_amount) as PMTD_funded_Amount
from bank_loan
where MONTH(issue_date) =  11 
  AND YEAR(issue_date) = 2021
 
 
---Total Amount Received
Select SUM(total_payment) AS Total_Amount_Received
from bank_loan
where MONTH(issue_date) = 12 
   AND YEAR(issue_date) = 2021
 
 
   
---MTD Total Amount Received
Select SUM(total_payment) AS MTD_Amount_Received
from bank_loan
where MONTH(issue_date) = 12 
   AND YEAR(issue_date) = 2021
 
   
---PMTD Total Amount Received
Select SUM(total_payment) AS PMTD_Amount_Received
from bank_loan
where MONTH(issue_date) = 11 
   AND YEAR(issue_date) = 2021
 
 
---Average Interest Rate
Select Round(AVG(int_rate),4) * 100 as Average
from bank_loan
 
 
---MTD Averge Interest Rate
Select Round(AVG(int_rate),4) * 100 as MTD_Average
from bank_loan
where MONTH(issue_date) = 12
    AND YEAR(issue_date) = 2021
 
 
---PMTD Averge Interest Rate
Select Round(AVG(int_rate),4) * 100 as MTD_Average
from bank_loan
where MONTH(issue_date) = 11
    AND YEAR(issue_date) = 2021
 
 
---Average DTI(Debt to income Ratio) 
Select ROUND(AVG(dti),4) * 100 as Avg_DTI 
from bank_loan
 
 
---MTD Average DTI(Debt to income Ratio) 
Select ROUND(AVG(dti),4) * 100 as MTD_Avg_DTI 
from bank_loan
where MONTH(issue_date) = 12
  AND YEAR(issue_date) = 2021
 
 
---PMTD Average DTI(Debt to income Ratio) 
Select ROUND(AVG(dti),4) * 100 as PMTD_Avg_DTI 
from bank_loan
where MONTH(issue_date) = 11
  AND YEAR(issue_date) = 2021
 
 
 
======================Good vs Bad Loans==========================
--Good Loan Percentage
Select CONCAT((COUNT(CASE WHEN loan_status IN ('Fully Paid','Current') then id END) * 100)
         /
       COUNT(id),'%') as Good_Loan_Percentage
from bank_loan
 
 
--Good Loan Applications
Select COUNT(id) as Good_Loan_Application 
from bank_loan
where loan_status IN ('Fully Paid','Charged')
 
 
--Good Loan Funded Amount
Select SUM(loan_amount) as Good_Loan_Funded_Amount
from bank_loan
where loan_status IN ('Fully Paid','Charged')
 
 
--Good Loan Payment Received
Select SUM(total_payment) as Good_Loan_Total_Payment
from bank_loan
where loan_status IN ('Fully Paid','Charged')
 
 
=====================================================================================
 
--Bad Loan Percentage
Select CONCAT((COUNT(CASE When loan_status = 'Charged Off' then id END) * 100)
        /
   COUNT(id),'%') as Bad_Loan_Percentage
from bank_loan
 
 
--Bad Loan Applications
Select COUNT(id) as Bad_loan_Application
from bank_loan
Where loan_status = 'Charged off'
 
 
--Bad Loan Funded Amount
Select SUM(loan_amount) as Bad_Loan_Funded_Amount
from bank_loan
Where loan_status = 'Charged off'
 
 
--Bad Loan Payment Received
Select SUM(total_payment) as Bad_Loan_Amount_Received
from bank_loan
Where loan_status = 'Charged off'
 
 
===================================LOAN STATUS =============================================
Select loan_status,
   SUM(loan_amount) as MTD_Funded_Amount,
   SUM(total_payment) as MTD_Payment_Received   
from bank_loan
where MONTH(issue_date) = 12
Group by loan_status
 
 
Select loan_status,
       COUNT(id) as Total_Loan_Applications,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received,
   Avg(int_rate * 100) as Average_Interest_Rate,
   Avg(dti * 100) as Average_debt_to_income_ratio
from bank_loan
Group by loan_status
 
=======================================Overview===========================
----1)
Select MONTH(issue_date) as MONTH_NO,
       DATENAME(MONTH,issue_date) as MONTHNAME,   
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by DATENAME(MONTH,issue_date),MONTH(issue_date)
Order by MONTH_NO asc
 
----2)
Select address_state as STATE,
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by address_state
Order by count(id) desc
 
----3)
Select term as TERM,
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by term
Order by Count(id) DESC
 
----4)
Select emp_length,
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by emp_length
Order by Count(id) DESC
 
----5)
Select purpose as Loan_Purpose,
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by purpose
Order by Count(id) DESC
 
----6)
Select home_ownership as 'Home Ownership',
       Count(id) as Total_Loan_Application,
   SUM(loan_amount) as Total_Funded_Amount,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by home_ownership
Order by Count(id) DESC
 
 
=======================================Details===========================
Select id as ID,
       purpose as Purpose,
   home_ownership as 'Home Ownership',
       grade as Grade,
   sub_grade as 'Sub Grade',
   issue_date as 'Issue Date',
   SUM(loan_amount) as Total_Funded_Amount,
   int_rate as Interest_Rate,
   installment as Installment,
   SUM(total_payment) as Total_Payment_Received
from bank_loan
Group by id,purpose,home_ownership,int_rate,
         grade,sub_grade,issue_date,installment
Order by id desc
 
 
 
 
----3rd Highest Grade
With CTE
AS
 (Select Grade as GRADE,
COUNT(id) as Good_Loan_Applications,
DENSE_RANK() Over(order by count(*) desc) as RNK
from bank_loan
where loan_status IN ('Fully Paid','Current')
Group by Grade )
 
Select GRADE,
Good_Loan_Applications
from CTE
where RNK = 3