题解 | 贷款情况

WITH t1 AS (
    SELECT city,loan_type_name,l1.loan_type_id,
           ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(loan_type_name) DESC , l1.loan_type_id ASC) AS rnk
    FROM loan_applications l 
    LEFT JOIN customers c ON l.customer_id=c.customer_id
    LEFT JOIN loan_application_types l1 ON l.application_id=l1.application_id
    LEFT JOIN loan_types l2 ON l1.loan_type_id=l2.loan_type_id
    GROUP BY city,loan_type_name,l1.loan_type_id
),
t2 AS (
    SELECT c.city,SUM(loan_amount) AS total_loan_amount,
           ROUND(SUM(loan_amount)/COUNT(DISTINCT l.customer_id),2) AS average_loan_amount,
           COUNT(DISTINCT l.customer_id) AS total_customers
    FROM loan_applications l 
    LEFT JOIN customers c ON l.customer_id=c.customer_id
    LEFT JOIN loan_application_types l1 ON l.application_id=l1.application_id
    LEFT JOIN loan_types l2 ON l1.loan_type_id=l2.loan_type_id
    GROUP BY c.city
)
SELECT t1.city,total_loan_amount,average_loan_amount,total_customers,
       loan_type_name AS most_applied_loan_type
FROM t1 
LEFT JOIN t2 ON t1.city=t2.city
WHERE rnk=1
ORDER BY t1.city