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