题解 | SQLW3 分析客户逾期情况

WITH t1 AS (
    SELECT pay_ability,
           count(pay_ability) AS level_num
    FROM customer_tb
    GROUP BY pay_ability
),
t2 AS (
    SELECT b.pay_ability,
           COUNT(overdue_days) AS over_num
    FROM loan_tb a
    LEFT JOIN customer_tb b
    ON a.customer_id = b.customer_id
    GROUP BY b.pay_ability
),
t3 AS (
    SELECT t1.pay_ability,
           ROUND(over_num / level_num * 100, 1) AS overdue_ratio
    FROM t1
    LEFT JOIN t2
    ON t1.pay_ability = t2.pay_ability
    ORDER BY overdue_ratio DESC
),
t4 AS (
    SELECT
        pay_ability,
        concat(overdue_ratio,'%') AS overdue_ratio
    FROM t3
)
SELECT * FROM t4;

# 这道题的关键在于 count（字段），自动过滤 null 值。