Analyzing Lending Club Data: SQL Queries and Insights
School
University of New South Wales**We aren't endorsed by this school
Course
ANAT 4000
Subject
Information Systems
Date
Dec 11, 2024
Pages
18
Uploaded by shahrozekhan216
DATA4200 Assessment 2Shahroze-18124651DATA4200Assessment 2 Report“Lending Club”Results & InterpretationsPrepared by:Shahroze Ahmed Khan1812465
DATA4200 Assessment 2Shahroze-1812465Section #1: Server Connection and Simple SQL QueriesPart 1: Updating the table to add a columnQuery:Result:Interpretation:The missing column ‘recoveries’ is added into the Lending club table using the ‘Alter table’query, before importing the data.Part 2: Simple SELECT QueryQuery:2
DATA4200 Assessment 2Shahroze-1812465Result:Interpretation:The above query is used to display first 10 rows out of total 10,000 rows of the Lending clubtable.Section #2: SQL for Business InsightsPart 1(a): Loan and Funded Amounts Query:Result:3
DATA4200 Assessment 2Shahroze-1812465Interpretation:All the loan details from the table have been sorted in the ascending order of the fundedamount in 4thcolumn, with the least funded amount ‘$725’ on top and followed by greaterfunded amounts.Part 1(b): No. of customers and Funded AmountsQuery and Result:4
DATA4200 Assessment 2Shahroze-1812465Interpretation:Based on the above queries and their results, it can be interpretated that around 62% of thetotal customers were funded with the amount in excess of $10,000, however, around 31% ofthe customers received funds below $10,000 and a very small proportion of customersprecisely 7% received exact $10,000.Part 2: Loan Terms Query:Result:Interpretation:The lending club prefers offering around 70% loans for shorter term of 3 years; however, thenumber of loans seem to decrease when the loan term is of 5 years, which might be due to thenon-verified source of income over longer term. Part 3: Interest Rate Query 5
DATA4200 Assessment 2Shahroze-1812465Result:Interpretation:The lending club is charging a minimum interest rate of 5.32% and a maximum 28.49% rateof interest. Moreover, they are charging a mean interest rate of 13.2% across all the loansbeing offered.Part 4(a): Loan StatusQuery:Result:6
DATA4200 Assessment 2Shahroze-1812465Interpretation:It can be deduced from the above table that around 68% of the total number of loans arecurrently active and still being paid, however, 24% of the loans have been paid offcompletely. 5% of the loans have been charged off while 1.5 % of the loans i.e., 150 loans arepast due. Part 4(b): 100 rows of data for each loan statusQueries and Results:oLoan_status = ‘Charged off’oLoan_status = ‘Current’7
DATA4200 Assessment 2Shahroze-1812465oLoan_status = ‘Default’oLoan_status = ‘Does not meet the credit policy. Status: Charged Off’8
DATA4200 Assessment 2Shahroze-1812465oLoan_status = ‘Does not meet the credit policy. Status: Fully Paid’oLoan_status = ‘Fully Paid’9
DATA4200 Assessment 2Shahroze-1812465oLoan_status = ‘In Grace Period’oLoan_status = ‘Issued’10
DATA4200 Assessment 2Shahroze-1812465oLoan_status = ‘Late (16-30 days)’oLoan_status = ‘late (31-120 days)’11
DATA4200 Assessment 2Shahroze-1812465Interpretation:By limiting 100 rows of data for each loan status, some useful insights about loan terms andborrower profiles can be extracted and compared across all the loan statuses. Of all the loanstatuses, the above queries managed to provide full 100 rows of data for loan statuses:‘Charged Off’, ‘Current’, ‘Fully Paid’ and ‘Late (31-120 days)’. However, the remaining loanstatuses also yielded rows of data which were less than 100, and the lowest of all the datareceived is 8 rows of data for ‘Does not meet the credit policy. Status: Charged Off’,followed by 11 rows of data for ‘Default’ which represents that a very small proportion ofcustomers have defaulted their loan obligations. Part 5(a): Loan GradesQuery:Result:Interpretation:12
DATA4200 Assessment 2Shahroze-1812465Grade ‘B’ and Grade ‘C’ are the most popular loan grades, having the highest number ofloans among all the loan grades followed by Grade ‘A’ and Grade ‘D’. However, the loangrade ‘G’ has the lowest number of loans contributing only 0.61% of all loans.Part 5(b): Loan Sub-GradesQuery:Result:Interpretation:Every grade is further divided into 5 sub-grades and the loan distribution differs acrossdistinct loan sub-grades, offering understandings of loan classification. Within grade ‘A’, thesub-grade ‘A5” has the highest no. of loans, similarly for grade ‘B’, sub-grade ‘B3’ is themost popular loan category.Part 6: Loan Defaults/DelinquenciesQuery:13
DATA4200 Assessment 2Shahroze-1812465Result:Interpretation:The defaulted customers include those with ‘default’ and ‘late” loan statuses. Therefore, atotal of 161 customers have defaulted their loan obligations and are past due by (16-120days).Section #3: SQL and Data Visualization for Business Insights1.Donut Chart: “Sum of funded amount by term and verification status”14
DATA4200 Assessment 2Shahroze-1812465This donut chart visualization and data dispersion emphasizes on the significance ofincome source verification in allocation of fundings and its impact on the amount andterm of loans. The red portions represent the sum of funded amounts for each verification status fora term of 36 months, while the blue portions represent the same data for a 60-monthterm.This chart demonstrates that a substantial portion around 60% of the funded amount isallotted to the loans having a term of 36-months represented by red colour,predominantly for ‘Source verified’ and ‘Verified’ applicants.For both the loan term categories, it can be interpreted that the ‘Not Verified’ loansare more diversely distributed being 25M for 36 months term and falling to 8M for a60-month term, signifying a diverse risk profile on the basis of verification status.Highlighting the fact, to achieve a long-term loan it is necessary to have a verifiedsource of income.2.Line Chart: “Average interest rate by Year and Grade”This line chart illustrates the trend of average of interest rates over the years andvarious loan grades. Grade A loan (light blue) represents a declining trend of the average interest rates,ranging from 8.5 % in 2008 to 7% in 2015, suggesting a trend of lower riskassociation and enhanced creditworthiness with borrowers of loan grade A.15
DATA4200 Assessment 2Shahroze-1812465Grade B (Grey), Grade C (Orange), Grade D (Green) and Grade E (Pink)demonstrates a fluctuating trend with some rise and falls in average interest rates overthe years. This erraticism implies the sensitivity of the market along with the diverseprofiles of borrowers for these categories of grade. Grade F (Purple) and Grade G (Yellow) displays a steady rise of average interest ratesfrom 16.3% and 17.2% in 2007 respectively to 24.3% and 25.9% in 2014respectively. This rising trend proposes a higher risk association along with someeconomic factors which might stimulus interest rates for borrowers of grade F andgrade G loans. Grade G continues to rise in 2015, however, grade F experiences adrop in average interest rates in 2015.3.Stacked Column Chart: “Sum of loan amount and count of member by year andhome ownership”This stacked column chart portrays the sum of loan amount and the number ofcustomers over the years with different categories of home ownership.Each column specifies a year ranging from 2007 to 2015. Moreover, these columnsare segmented into various categories of home ownership such as Mortgage, Own,Rent. Each segment height in a column denotes the sum of disbursed loan amounts tothe borrowers for the categories of home ownership across the years.The rising trendline represents the increasing number of customers from 2007 to 2015which proposes the success of lending club in penetrating the market, attaining moreborrowers and expansion of the customer base.16
DATA4200 Assessment 2Shahroze-1812465The distribution of loan amount by categories of home ownership highlights thepreferences and demographics of the borrower. From 2007 to 2011, the loan amountand the number of customers experienced a gradual growth, however there was asignificant and steep growth from 2011 onwards where the lending amount for‘Mortgage’ experienced the highest growth from 4M in 2012 to 39M in 2015reflecting variations in the interest rates and the housing market, followed by‘Renting’ which is the second-best home ownership preference of the borrowers.**Kinds of Customers that default on their loans**Borrowers with lower credit scores and poor histories are more prone to default.Therefore, the credit reports should be evaluated cautiously by the lenders.Customers having a high DTI (debt to income) value are at high risk of defaulting, ashigher DTI value means that the installment payments are higher than the income,resulting into a struggle of making payments. This may be due to lower annualincome, shorter tenures of employment and not verified income source.The loan purpose can also be indicative of the risk of default, since the customersborrowing money for purposes such as debt consolidation are more likely to default.17
DATA4200 Assessment 2Shahroze-181246518