题解 | 贷款情况

/*
①关联4个表，展示每一个贷款的相关信息
②以城市和贷款类型分组求出每个城市最常申请的贷款类型名称
③以城市分组求出其他指标
④关联两个表求出结果
*/

WITH
t1 AS (     -- 关联4个表，展示每一个贷款的相关信息
SELECT
    kehu.customer_id,
    kehu.city,
    daikuan.loan_amount,
    leixing.loan_type_id,
    leixing.loan_type_name
FROM customers AS kehu
LEFT JOIN loan_applications AS daikuan ON kehu.customer_id = daikuan.customer_id
LEFT JOIN loan_application_types AS fenlei ON daikuan.application_id = fenlei.application_id
LEFT JOIN loan_types AS leixing ON fenlei.loan_type_id = leixing.loan_type_id
),

t2 AS (     -- 以城市和贷款类型分组,使用窗口函数对各产品类型的销量进行排名
SELECT 
    city,
    loan_type_id,
    loan_type_name,
    ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(*) DESC,loan_type_id) AS rk
FROM t1
GROUP BY city,loan_type_name,loan_type_id
),

t3 AS (     -- 以城市分组求出其他指标
SELECT
    city,
    ROUND(SUM(loan_amount),2) AS total_loan_amount,
    ROUND(SUM(loan_amount) / COUNT(DISTINCT customer_id),2) AS average_loan_amount,
    COUNT(DISTINCT customer_id) AS total_customers
FROM t1
GROUP BY city
)

SELECT      -- 主查询，关联清洗后的数据，依题意输出结果
    t3.city,
    t3.total_loan_amount,
    t3.average_loan_amount,
    t3.total_customers,
    t2.loan_type_name AS most_applied_loan_type
FROM t3
LEFT JOIN t2 ON t3.city = t2.city AND t2.rk = 1
ORDER BY t3.city;