Banking Case Study

Workflow :-
1. Group the data of loan by loan id
2. Group the client data by client id
3. Generate Age from client data and store
4. Get the transaction data for last year
5. Sum up the data based on transaction type and amount
6. Group the card data by disposition id
7. Group the district data by district id
8. Filter out the un employment data for the year 95 & 96 from district
9. Generate the difference between the unemployment data for every district for those two years
10. Group the disposition data
11. Joining :-
join loan,transaction,Account,Disposition,on ac_id as ac_id_join
join ac_id_join,district_info,client on district_id as include_district
join include_district,card on disposition_id as join_done
select loan_amount,loan_duration,loan_status,type,transaction_amount,date,owner_type,district_name,region,avg_salary,unemployment_rate_95,unemployment_rate_96,no_of_enterpreneur/1000,card type,birthday

12. Algorithm used to predict excellent, good and risky customers:

12.1.within 1 year {

if transaction_amount > 10 lac and avg_sal > 10k and loan_status==’A’ and (age >25 and age <=65)

write in a file called good more loan can be granted card can be upgrade

12.2.  if transaction_amount > 10 lac and avg_sal > 6k and loan_status==’A’ and loan_status==’C’ and (age >25 and age <=55) and unemployment_rate < 0.80

write in a file called ok more loan can be granted after completion of the loan card can be upgraded after completion of the loan

12.3.  if avg_sal > 6k and loan_status==’B’ and loan_status==’D’ and (age >35) and no_of_entrepreneur>100

write in a file called risky no more loans card must be downgraded

}