Boosting_Algorithms/sql_eda.sql at main · wessware/Boosting_Algorithms · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
-- Basic EDA queries for the loan_data_clean table

-- view the rows of the cleaned loan data
SELECT * from loan_data_clean

--view the total number of loans in the dataset
SELECT COUNT(*) as total_loans FROM loan_data_clean;

--view the unique loan statuses in the dataset
SELECT DISTINCT Loan_Status FROM loan_data_clean;

--view the count of each loan status in the dataset
SELECT Loan_Status, COUNT(*) as count FROM loan_data_clean GROUP BY Loan_Status;

--view the minimum, maximum, average loan amount
SELECT Loan_Status,
MIN(LoanAmount) as Minimum,
MAX(LoanAmount) as Maximum,
AVG(LoanAmount) as Average FROM loan_data_clean

GROUP BY Loan_Status;

--view the average applicant income
SELECT AVG(ApplicantIncome) as Average_Income FROM loan_data_clean;

SELECT Loan_Status,
AVG(AppliantIncome) as Average_Income from loan_data_clean
GROUP BY Loan_Status;

SELECT Loan_Status,
AVG(LoanAmount) as Average_Loan_Amount from loan_data_clean
GROUP BY Loan_Status;


SELECT MIN(LoanAmount) as Min_Loan_Amount,
MAX(LoanAmount) as Max_Loan_Amount FROM loan_data_clean;

SELECT MIN(ApplicantIncome) as Minimum_Income,
MAX(ApplicantIncome) as Maximum_Income FROM loan_data_clean;

SELECT Property_Area, COUNT(*) as Count from loan_data_clean
GROUP BY Property_Area;

SELECT Education, COUNT(*) as Count from loan_data_clean
GROUP BY Education;


SELECT ApplicantIncome, LoanAmount from loan_data_clean
ORDER BY ApplicantIncome DESC;

--INSIGHT ANALYSIS QUERIES

SELECT Gender,
AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Gender;


SELECT Married,
AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Married;


SELECT Self_Employed,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Self_Employed;


SELECT Credit_History,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Credit_History;


SELECT Property_Area,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Property_Area;

SELECT Gender,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as
LOAN_APPROVAL_RATE from loan_data_clean

GROUP BY Gender;

SELECT Married,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as
LOAN_APPROVAL_RATE from loan_data_clean

GROUP BY Married;


SELECT Education,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Education;


SELECT Dependents,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Dependents;

SELECT Property_Area,

AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT from loan_data_clean

GROUP BY Property_Area;


SELECT Self_Employed,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as LOAN_APPROVAL_RATE
from loan_data_clean

GROUP BY Self_Employed;


SELECT ApplicantIncome, LoanAmount, LoanAmount/ApplicantIncome as
LOAN_INCOME_RATIO from loan_data_clean

WHERE ApplicantIncome IS NOT NULL AND LoanAmount IS NOT NULL

ORDER BY LOAN_INCOME_RATIO ASC;

SELECT Property_Area,
SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as Default_Rate
from loan_data_clean

GROUP BY Property_Area;


SELECT Education, Self_Employed,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as
LOAN_APPROVAL_RATE from loan_data_clean

GROUP BY Education, Self_Employed;


SELECT ApplicantIncome, LoanAmount from loan_data_clean
WHERE ApplicantIncome < 100 AND LoanAmount > 100;


SELECT Credit_History, Property_Area,
AVG(LoanAmount) as AVERAGE_LOAN_AMOUNT,
AVG(ApplicantIncome) as AVERAGE_APPLICANT_INCOME

from loan_data_clean

GROUP BY Credit_History, Property_Area;


SELECT Gender, Married,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*) as LOAN_APPROVAL_RATE

from loan_data_clean

GROUP BY Gender, Married;


SELECT Dependents, Property_Area,

SUM(CASE WHEN Loan_Status=1 THEN 1 ELSE 0 END)*100/COUNT(*)
as LOAN_APPROVAL_RATE
from loan_data_clean

GROUP BY Dependents, Property_Area;


SELECT LoanAmount, ApplicantIncome FROM loan_data_clean

WHERE Credit_History=0 AND LoanAmount > 150

ORDER BY LoanAmount DESC;


SELECT Education, Loan_Status,

COUNT(*) as COUNT from loan_data_clean

GROUP BY Education, Loan_Status;


SELECT

SUM(CASE WHEN Loan_Status=0 THEN LoanAmount ELSE 0 END) as Defaulted_Loan_Amount,
SUM(CASE WHEN Loan_Status=0 THEN LoanAmount ELSE 0 END)*100/SUM(LoanAmount)

as Portfolio_risk_percentage

from loan_data_clean;