题解 | 贷款情况

```sql
WITH customer_avg AS (
    -- 1. 先计算每个客户的总贷款金额
    SELECT 
        c.city,
        c.customer_id,
        SUM(la.loan_amount) AS customer_total_loan
    FROM loan_applications la
    INNER JOIN customers c ON la.customer_id = c.customer_id
    GROUP BY c.city, c.customer_id
),
city_loan_stats AS (
    -- 2. 计算每个城市每个贷款类型的申请次数
    SELECT 
        c.city,
        lt.loan_type_id,
        lt.loan_type_name,
        COUNT(*) AS type_count
    FROM loan_applications la
    INNER JOIN customers c ON la.customer_id = c.customer_id
    INNER JOIN loan_application_types lat ON la.application_id = lat.application_id
    INNER JOIN loan_types lt ON lat.loan_type_id = lt.loan_type_id
    GROUP BY c.city, lt.loan_type_id, lt.loan_type_name
),
ranked_loan_types AS (
    -- 3. 对每个城市的贷款类型按申请次数降序、loan_type_id升序排名
    SELECT 
        city,
        loan_type_id,
        loan_type_name,
        type_count,
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY type_count DESC, loan_type_id ASC) AS rn
    FROM city_loan_stats
),
city_summary AS (
    -- 4. 计算每个城市的贷款总额、平均每人贷款金额、客户数
    SELECT 
        ca.city,
        ROUND(SUM(ca.customer_total_loan), 2) AS total_loan_amount,
        ROUND(AVG(ca.customer_total_loan), 2) AS average_loan_amount,  -- 这里是人均贷款金额的平均值
        COUNT(ca.customer_id) AS total_customers
    FROM customer_avg ca
    GROUP BY ca.city
)
-- 5. 最终结果：合并城市统计数据和最常申请的贷款类型
SELECT 
    cs.city,
    cs.total_loan_amount,
    cs.average_loan_amount,
    cs.total_customers,
    rlt.loan_type_name AS most_applied_loan_type
FROM city_summary cs
LEFT JOIN ranked_loan_types rlt ON cs.city = rlt.city AND rlt.rn = 1
ORDER BY cs.city ASC;
```

修正的关键点：

1. 新增customer_avg CTE：先计算每个客户的总贷款金额
   · New York的客户1有两笔贷款：10000 + 30000 = 40000
   · New York的客户3有一笔贷款：20000
2. 修正city_summary中的计算：
   · total_loan_amount：所有客户贷款金额的总和 (40000 + 20000 = 60000)
   · average_loan_amount：客户人均贷款金额的平均值 ((40000 + 20000) / 2 = 30000)
   · total_customers：客户数量 (2)
3. 最常申请的贷款类型逻辑保持不变

这样New York的平均贷款金额就会正确显示为30000，而不是原来错误计算的值。