sql/04_segments.sql

-- Default rates by segment, with each segment's share of accounts and of-- defaults from window functions. A segment that holds far more of the-- defaults than of the accounts is where the risk concentrates. WITH segmented AS (    SELECT 'Most recent status' AS dimension,           status_sep_group      AS segment,           CASE status_sep_group               WHEN 'No use (-2)' THEN 1 WHEN 'Paid duly (-1)' THEN 2 WHEN 'Revolving (0)' THEN 3               WHEN '1 month late' THEN 4 WHEN '2 months late' THEN 5 ELSE 6 END AS sort_key,           default_next_month    FROM account_features    UNION ALL    SELECT 'Utilisation, September',           CASE WHEN utilisation_sep <= 0 THEN 'None or credit'                WHEN utilisation_sep < 0.3 THEN 'Under 30%'                WHEN utilisation_sep < 0.7 THEN '30% to 70%'                WHEN utilisation_sep <= 1.0 THEN '70% to 100%'                ELSE 'Over limit' END,           CASE WHEN utilisation_sep <= 0 THEN 1 WHEN utilisation_sep < 0.3 THEN 2                WHEN utilisation_sep < 0.7 THEN 3 WHEN utilisation_sep <= 1.0 THEN 4 ELSE 5 END,           default_next_month    FROM account_features    UNION ALL    SELECT 'Months delayed, last six',           CASE WHEN months_delayed_6m = 0 THEN 'None'                WHEN months_delayed_6m <= 2 THEN '1 to 2'                WHEN months_delayed_6m <= 4 THEN '3 to 4'                ELSE '5 to 6' END,           CASE WHEN months_delayed_6m = 0 THEN 1 WHEN months_delayed_6m <= 2 THEN 2                WHEN months_delayed_6m <= 4 THEN 3 ELSE 4 END,           default_next_month    FROM account_features    UNION ALL    SELECT 'Credit limit, NT$',           CASE WHEN credit_limit < 50000 THEN 'Under 50k'                WHEN credit_limit < 150000 THEN '50k to 150k'                WHEN credit_limit < 300000 THEN '150k to 300k'                ELSE '300k and over' END,           CASE WHEN credit_limit < 50000 THEN 1 WHEN credit_limit < 150000 THEN 2                WHEN credit_limit < 300000 THEN 3 ELSE 4 END,           default_next_month    FROM account_features)SELECT dimension,       segment,       COUNT(*)                                                              AS accounts,       SUM(default_next_month)                                               AS defaults,       ROUND(AVG(default_next_month), 4)                                     AS default_rate,       ROUND(1.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY dimension), 4) AS share_of_accounts,       ROUND(1.0 * SUM(default_next_month)             / SUM(SUM(default_next_month)) OVER (PARTITION BY dimension), 4) AS share_of_defaultsFROM segmentedGROUP BY dimension, segment, sort_keyORDER BY dimension, sort_key;