Loan Risk Ranking

Business Scenario

Welcome!

Today is your seventh day as a Data Analyst at our company.

 

The client wants us to build a Loan Risk Ranking System to analyze customer loan applications and identify high-risk borrowers based on credit score, loan amount, income, and repayment history.

Pre-Lab Preparation

In This System, Loan Managers Should Be Able To:

  • Rank customers based on loan risk score.

  • Assign row numbers to applications within each loan category.

  • Calculate cumulative loan exposure.

  • Compare each applicant’s risk with previous and next applicants.

Topic : Mastering SQL Window Functions

1) Understanding OVER()

2)Using RANK(), DENSE_RANK(), and ROW_NUMBER()

3)Calculating Running Totals with SUM() OVER()

4)Comparing Rows with LAG() and LEAD()

5)Partitioning Data with PARTITION BY

Git Pull

git pull origin branchName

Task 1: Rank Loan Applications by Risk Score

Before approving any loan, the bank wants to identify which applicants pose the highest level of financial risk. By ranking customers according to their risk score, loan officers can quickly determine which applications require stricter review and additional verification.

Applicants with higher risk scores are considered more likely to default, so they receive higher priority during risk assessment.

To perform this analysis, we use the RANK() window function, which assigns a rank to each applicant based on their risk score in descending order.

Table: loan_applications

application_idcustomer_nameloan_typeloan_amountannual_incomecredit_scorerisk_score
1001Amit SharmaPersonal Loan50000060000068082
1002Priya
Patel		Home Loan3500000120000075045
1003Rahul VermaAuto Loan80000070000069070
1004Sneha
Joshi		Personal Loan30000040000062090
1005Nitin JainBusiness Loan1500000180000071055
SELECT application_id,
       customer_name,
       risk_score,
       RANK() OVER (ORDER BY risk_score DESC) AS risk_rank
FROM loan_applications;

Task 2: Assign Row Numbers Within Each Loan Type

The bank wants to organize loan applications by assigning a unique sequence number within each loan category.

This makes it easier to compare applicants applying for the same type of loan, such as Home Loan or Personal Loan.

The numbering restarts for each loan type, ensuring that every category is analyzed independently

Table: loan_applications

application_idcustomer_nameloan_typeloan_amountannual_incomecredit_scorerisk_score
1001Amit SharmaPersonal Loan50000060000068082
1002Priya
Patel		Home Loan3500000120000075045
1003Rahul VermaAuto Loan80000070000069070
1004Sneha
Joshi		Personal Loan30000040000062090
1005Nitin JainBusiness Loan1500000180000071055
SELECT application_id,
       customer_name,
       loan_type,
       risk_score,
       ROW_NUMBER() OVER (
           PARTITION BY loan_type
           ORDER BY risk_score DESC
       ) AS row_num
FROM loan_applications;

Task 3: Analyze Running Total of Loan Amounts

After ranking loan applications by risk score, the bank wants to understand how its total loan exposure increases as each application is considered.

By arranging applicants from highest to lowest risk and calculating a running total of loan amounts, loan officers can see the cumulative amount at risk at every step. This helps management assess how much capital is exposed to borrowers with varying risk levels.

Table: loan_applications

application_idcustomer_nameloan_typeloan_amountannual_incomecredit_scorerisk_score
1001Amit SharmaPersonal Loan50000060000068082
1002Priya
Patel		Home Loan3500000120000075045
.....................
1005Nitin JainBusiness Loan1500000180000071055
SELECT application_id,
       customer_name,
       loan_amount,
       risk_score,
       SUM(loan_amount) OVER (
           ORDER BY risk_score DESC
       ) AS running_total
FROM loan_applications;

Task 4: Compare rows using analytical functions

Once the loan applications have been ranked and organized, the risk assessment team wants to look deeper into how each applicant compares with others in the list.

As they review the report, they compare every customer’s risk score and loan amount with the applicants listed immediately before and after them.

To perform this step-by-step comparison, we use analytical functions

If they notice a sudden jump in risk score or a significant increase in loan amount, that application is flagged for additional scrutiny.

1

Compare with the Previous Applicant Using LAG()

application_idcustomer_
name		loan_
​type		loan_
​amount		annual_
​income		credit_
​score		risk_
​score
1001 Amit SharmaPersonal Loan50000060000068082
1002Priya PatelHome Loan3500000120000075045
1003Rahul VermaAuto Loan80000070000069070
1004Sneha JoshiPersonal Loan30000040000062090
.....................
1015Karan MalhotraBusiness Loan1750000190000072053
SELECT application_id,
       customer_name,
       risk_score,
       LAG(risk_score) OVER (ORDER BY risk_score DESC) AS previous_risk_score
FROM loan_applications;

2

Compare with the Next Applicant Using LEAD()

application_idcustomer_
name		loan_
​type		loan_
​amount		annual_
​income		credit_
​score		risk_
​score
1001 Amit SharmaPersonal Loan50000060000068082
1002Priya PatelHome Loan3500000120000075045
1003Rahul VermaAuto Loan80000070000069070
1004Sneha JoshiPersonal Loan30000040000062090
.....................
1015Karan MalhotraBusiness Loan1750000190000072053
SELECT application_id,
       customer_name,
       risk_score,
       LEAD(risk_score) OVER 
       (ORDER BY risk_score DESC) 
       AS next_risk_score
FROM loan_applications;

 

Great job!
You have successfully completed your first lab on BiteBox Project Onboarding.

In this lab, you have: Understood the BRD, Created a wireframe, Set up your development environment, Organised your project structure, Run your first program

You are now ready to move to the next stage of development

Checkpoint

Next-Lab Preparation

   Git Push

git push origin branchName

Topic : Working with a Text and Listin HTML

1) Power of HTML text tags
2) Customizing your style with CSS
3) Listing it right using HTML
4) HTML Link up , attributes of tag, block vs inline elements

Text box Width : 887
Business Scenario, Pre-lab Preparation, Next-lab Preparation, Task, Activity, Checkpoint : 90%.
Steps : 1,2,3 [Sub Steps - a,b,c]
Normal Text, Topic Name : 80%
Subtopic : 70%
Code Box font Size : 16px