题解 | 贷款情况

WITH t1 AS(
    SELECT
        c.city,
        lt.loan_type_name,
        COUNT(lat.loan_type_id) AS cnt
    FROM
        customers c LEFT JOIN loan_applications la
        ON c.customer_id = la.customer_id
        LEFT JOIN loan_application_types lat
        ON la.application_id = lat.application_id
        LEFT JOIN loan_types lt
        ON lat.loan_type_id = lt.loan_type_id
    GROUP BY
        c.city, lt.loan_type_name
),
t2 AS(
    SELECT
        *
    FROM
        (SELECT
            t1.city,
            t1.loan_type_name,
            ROW_NUMBER() OVER(
                PARTITION BY t1.city
                ORDER BY cnt DESC, lt.loan_type_id ASC
            ) AS rk
        FROM
            t1 LEFT JOIN loan_types lt
            ON t1.loan_type_name = lt.loan_type_name) tmp
    WHERE
        rk = 1
)
SELECT
    c.city,
    ROUND(SUM(la.loan_amount),2) AS total_loan_amount,
    ROUND(SUM(la.loan_amount) / COUNT(DISTINCT c.customer_id),2) AS average_loan_amount,
    COUNT(DISTINCT c.customer_id) AS total_customers,
    t2.loan_type_name AS most_applied_loan_type
FROM
    customers c LEFT JOIN loan_applications la 
    ON c.customer_id = la.customer_id
    LEFT JOIN t2
    ON c.city = t2.city
GROUP BY
    c.city, t2.loan_type_name
ORDER BY
    c.city ASC;