题解 | 分析客户逾期情况

-- 输出还款能力级别、逾期客户占比。
-- 注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序。

WITH T AS (SELECT C.customer_id, C.pay_ability, L.overdue_days
FROM customer_tb C LEFT JOIN loan_tb L
ON C.customer_id = L.customer_id)

SELECT pay_ability, 
CONCAT(ROUND(100 * ( SUM(CASE WHEN overdue_days IS NULL THEN 0 ELSE 1 END) / COUNT(*)),1), "%") AS overdue_ratio
FROM T
GROUP BY pay_ability
ORDER BY overdue_ratio DESC