sql/03_features.sql

-- Borrower behaviour features, built in SQL so every model input can be traced-- to a query. Demographic fields (sex, marital status, age, education) are left-- out on purpose: the model scores behaviour and credit usage only. DROP VIEW IF EXISTS account_features; CREATE VIEW account_features ASSELECT    account_id,    default_next_month,    credit_limit,     -- Most recent repayment status, grouped. Codes -2 and 0 are undocumented    -- and kept as their own groups so the data, not an assumption, sets their risk.    CASE        WHEN status_sep <= -2 THEN 'No use (-2)'        WHEN status_sep = -1 THEN 'Paid duly (-1)'        WHEN status_sep = 0 THEN 'Revolving (0)'        WHEN status_sep = 1 THEN '1 month late'        WHEN status_sep = 2 THEN '2 months late'        ELSE '3+ months late'    END AS status_sep_group,     -- Worst delay and number of delayed months over the six months.    MAX(status_sep, status_aug, status_jul, status_jun, status_may, status_apr) AS worst_status_6m,    (status_sep >= 1) + (status_aug >= 1) + (status_jul >= 1)      + (status_jun >= 1) + (status_may >= 1) + (status_apr >= 1) AS months_delayed_6m,     -- Utilisation: balance over limit, now and on average.    bill_sep / credit_limit AS utilisation_sep,    (bill_sep + bill_aug + bill_jul + bill_jun + bill_may + bill_apr) / (6.0 * credit_limit) AS avg_utilisation_6m,     -- Share of last month's bill paid this month. September's payment settles the    -- August statement; a zero or negative bill counts as fully paid.    CASE        WHEN bill_aug <= 0 THEN 1.0        ELSE MIN(paid_sep / bill_aug, 1.0)    END AS payment_ratio_sep,     -- Exposure inputs for the loss model.    MAX(bill_sep, 0) AS drawn_balance,    MAX(credit_limit - MAX(bill_sep, 0), 0) AS undrawn_limitFROM accounts;