Credit ScoreCard Model on SQLFlow

The credit scorecard model is a common model in the financial lending scenario. A scorecard model outputs a score representing how likely the lender will repay on time. This tutorial introduces how to train a credit scorecard model with SQLFlow.

Dataset Introduction

This tutorial using a public dataset Give Me Some Credit on the Kaggle website. The dataset contains 150,000 rows training data, and each row includes 11 columns, where serious_dlqin2yrs is the target column and represents person experienced 90 days past due delinquency or worse. The description for each column is as the table:

ColumnDescriptionType
serious_dlqin2yrsPerson experienced 90 days past due delinquency or worse1/0
revolving_utilization_of_unsecured_linesTotal balance on credit cards and personal lines of credit except for real estate and no installment debt like car loans divided by the sum of credit limitsFloat
ageAge of borrower in yearsInteger
number_of_time30_59days_past_due_not_worseNumber of times borrower has been 30-59 days past due but no worse in the last two years.Integer
debt_ratioMonthly debt payments, alimony, living costs divided by monthly gross incomeFloat
monthly_incomeMonthly incomeInteger
number_of_open_credit_lines_and_loansNumber of Open loans (installment like car loan or mortgage) and Lines of credit (e.g. credit cards)Integer
number_of_times_90_days_lateNumber of times borrower has been 90 days or more past dueInteger
number_real_estate_loans_or_linesNumber of mortgage and real estate loans including home equity lines of creditInteger
number_of_time60_89_days_past_due_not_worseNumber of times borrower has been 60-89 days past due but no worse in the last two yearsInteger
number_of_dependentsNumber of dependents in family excluding themselves (spouse, children, etc.)Integer

Dataset Loading and Preprocessing

Please note, you can skip this section and use the preprocessed dataset inner SQLFlow playground.

Download and extract the zip file from the download page, and execute the Python script give_me_some_credit.py to finish the data preprocess. The Python scripts would output a CSV file train.csv, and you can run the following SQL program to popularize the training table scorecard.train.

  1. DROP DATABASE IF EXISTS scorecard;
  2. CREATE DATABASE scorecard;
  3. DROP TABLE IF EXISTS scorecard.train;
  4. CREATE TABLE IF NOT EXISTS scorecard.train(
  5.   serious_dlqin2yrs int,
  6.   revolving_utilization_of_unsecured_lines float,
  7.   age int,
  8.   number_of_time30_59days_past_due_not_worse int,
  9.   debt_ratio float,
  10.   monthly_income float NULL,
  11.   number_of_open_credit_lines_and_loans int,
  12.   number_of_times_90_days_late int,
  13.   number_real_estate_loans_or_lines int,
  14.   number_of_time60_89_days_past_due_not_worse int,
  15.   number_of_dependents float
  16. );
  17. -- load train and test data from CSV files
  18. LOAD DATA LOCAL INFILE '/tmp/train.csv' INTO TABLE scorecard.train FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

Credit ScoreCard Modeling

You can have a glance at the training data by running the following SQL.

  1. %%sqlflow
  2. SELECT * FROM scorecard.train LIMIT 10;

Run the following command to start the credit scorecard modeling.

  1. SELECT * FROM scorecard.train
  2. TO TRAIN sqlflow_models.ScoreCard
  3. LABEL serious_dlqin2yrs
  4. INTO sqlflow_models.my_scorecard_model;

The above TRAIN clause would output the scorecard as the following. The final total score is the sum of all scores based on the independent variable’s value. The target score is 600, meaning that a user with a score higher than 600 will grant the credit.

  1.  TARGET SCORE: 600
  2.  age (20.999, 38.0] 106.0
  3.  age (38.0, 47.0] 108.0
  4.  age (47.0, 56.0] 101.0
  5.  age (56.0, 65.0] 99.0
  6.  age (65.0, 89.0] 93.0
  7.  debt_ratio (-0.001, 0.132] 87.0
  8.  debt_ratio (0.132, 0.287] 103.0
  9.  debt_ratio (0.287, 0.472] 93.0
  10.  debt_ratio (0.472, 3.248] 118.0
  11.  debt_ratio (3.248, 15466.0] 101.0
  12.  monthly_income (-0.001, 3332.4] 106.0
  13.  monthly_income (3332.4, 5212.6] 108.0
  14.  monthly_income (5212.6, 5400.0] 100.0
  15.  monthly_income (5400.0, 8180.0] 100.0
  16.  monthly_income (8180.0, 208333.0] 98.0
  17.  number_of_dependents (-0.001, 2.0] 103.0
  18.  number_of_dependents (2.0, 8.0] 98.0
  19.  number_of_open_credit_lines_and_loans (-0.001, 4.0] 108.0
  20.  number_of_open_credit_lines_and_loans (4.0, 6.0] 101.0
  21.  number_of_open_credit_lines_and_loans (6.0, 9.0] 98.0
  22.  number_of_open_credit_lines_and_loans (9.0, 12.0] 97.0
  23.  number_of_open_credit_lines_and_loans (12.0, 46.0] 108.0
  24.  number_of_time30_59days_past_due_not_worse (-0.001, 98.0] 103.0
  25.  number_of_time60_89_days_past_due_not_worse (-0.001, 98.0] 103.0
  26.  number_of_times_90_days_late (-0.001, 98.0] 103.0
  27.  number_real_estate_loans_or_lines (-0.001, 1.0] 101.0
  28.  number_real_estate_loans_or_lines (1.0, 2.0] 102.0
  29.  number_real_estate_loans_or_lines (2.0, 9.0] 119.0
  30.  revolving_utilization_of_unsecured_lines (-0.001, 0.0191] 74.0
  31.  revolving_utilization_of_unsecured_lines (0.0191, 0.0867] 66.0
  32.  revolving_utilization_of_unsecured_lines (0.0867, 0.276] 70.0
  33.  revolving_utilization_of_unsecured_lines (0.276, 0.686] 94.0
  34.  revolving_utilization_of_unsecured_lines (0.686, 2340.0] 139.0