题解 | 贷款情况

CREATE VIEW t1 AS 
        SELECT
            c.customer_id,
            c.customer_name,
            c.city,
            c.age,
            l.loan_amount,
            l.application_date,
            la.loan_type_id,
            t.loan_type_name,
            COUNT(t.loan_type_name) OVER (
                PARTITION BY c.city,t.loan_type_name) AS cnt
        FROM customers c
	    LEFT JOIN loan_applications l ON c.customer_id = l.customer_id
        LEFT JOIN loan_application_types la ON la.application_id = l.application_id
        LEFT JOIN loan_types t ON t.loan_type_id = la.loan_type_id;

WITH t2 AS (
        SELECT
            customer_id,
            city,
            loan_type_name,
            SUM(loan_amount) OVER (PARTITION BY city) AS total_loan_amount,
            ROUND(
                avg(loan_amount) OVER (PARTITION BY city),
                2
            ) AS average_loan_amount,
            ROW_NUMBER() OVER (
                PARTITION BY
                    city
                ORDER BY
                    cnt DESC,
                    loan_type_id ASC
            ) rn
        FROM
            t1
    ),
    t3 AS (
        SELECT
            city,
            COUNT(DISTINCT customer_id) AS total_customers
        FROM t1
        GROUP BY city
    )
SELECT
    t2.city,
    t2.total_loan_amount,
    ROUND(t2.total_loan_amount / t3.total_customers, 2) AS average_loan_amount,
    t3.total_customers,
    MAX(
        CASE
            WHEN t2.rn = 1 THEN t2.loan_type_name
        END
    ) AS most_applied_loan_type
FROM t2
LEFT JOIN t3 ON t2.city = t3.city
GROUP BY
    t2.city,
    t2.total_loan_amount,
    t2.average_loan_amount,
    t3.total_customers