贷款情况__牛客网

某金融服务公司希望分析其客户的贷款申请情况，以便更好地了解客户的行为模式和风险管理。具体来说，他们希望了解每个城市的客户贷款申请情况，包括每个城市的贷款申请总金额、平均贷款金额、客户数量以及最常申请的贷款类型。

【原始表】
loan_applications 表:

application_id: 贷款申请的唯一 ID，作为主键 (INT)
customer_id: 申请贷款的客户 ID (INT)
loan_amount: 申请的贷款金额 (DECIMAL)
application_date: 申请的日期 (DATE)

customers 表:

customer_id: 客户的唯一 ID，作为主键 (INT)
customer_name: 客户的姓名 (VARCHAR)
city: 客户所在的城市 (VARCHAR)
age: 客户的年龄 (INT)

loan_types 表:

loan_type_id: 贷款类型的唯一 ID，作为主键 (INT)
loan_type_name: 贷款类型的名称 (VARCHAR)

loan_application_types 表:

application_id: 与 loan_applications 表中的 application_id 相关联，表示贷款申请的 ID (INT)
loan_type_id: 与 loan_types 表中的 loan_type_id 相关联，表示贷款类型的 ID (INT)

【要求】
查询每个城市的客户贷款申请情况，包括每个城市的贷款申请总金额、平均贷款金额、客户数量以及最常申请的贷款类型（如果有多个贷款类型申请数量相同，则选择 loan_type_id 最小的那个），查询结果按照城市名称升序排列。
包含下面的字段：

city: 城市名称
total_loan_amount: 该城市所有客户的贷款申请总金额，保留小数点后2位。
average_loan_amount: 该城市所有客户的平均每个人的贷款申请金额，保留小数点后2位。
total_customers: 该城市的客户数量
most_applied_loan_type: 该城市最常申请的贷款类型名称

【示例输入】

loan_applications

customers

loan_types

loan_application_types

【示例输出】

输入

CREATE TABLE loan_applications (
    application_id INT PRIMARY KEY,
    customer_id INT,
    loan_amount DECIMAL(10, 2),
    application_date DATE
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50),
    age INT
);

CREATE TABLE loan_types (
    loan_type_id INT PRIMARY KEY,
    loan_type_name VARCHAR(50)
);

CREATE TABLE loan_application_types (
    application_id INT,
    loan_type_id INT,
    PRIMARY KEY (application_id, loan_type_id)
);

INSERT INTO loan_applications (application_id, customer_id, loan_amount, application_date) VALUES
(1, 1, 10000.00, '2023-01-01'),
(2, 2, 15000.00, '2023-02-01'),
(3, 3, 20000.00, '2023-03-01'),
(4, 4, 25000.00, '2023-04-01'),
(5, 1, 30000.00, '2023-05-01');

INSERT INTO customers (customer_id, customer_name, city, age) VALUES
(1, 'Alice', 'New York', 30),
(2, 'Bob', 'Los Angeles', 25),
(3, 'Charlie', 'New York', 35),
(4, 'David', 'Chicago', 28);

INSERT INTO loan_types (loan_type_id, loan_type_name) VALUES
(1, 'Personal Loan'),
(2, 'Home Loan'),
(3, 'Auto Loan');

INSERT INTO loan_application_types (application_id, loan_type_id) VALUES
(1, 1),
(2, 2),
(3, 1),
(4, 3),
(5, 2);

city|total_loan_amount|average_loan_amount|total_customers|most_applied_loan_type Chicago|25000.00|25000.00|1|Auto Loan Los Angeles|15000.00|15000.00|1|Home Loan New York|60000.00|30000.00|2|Personal Loan

WITH temp AS (SELECT DISTINCT city, loan_type_id, loan_type_name, COUNT(*) AS cts, ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(*) DESC,loan_type_id) AS rk FROM loan_applications LEFT JOIN customers USING (customer_id) LEFT JOIN loan_application_types USING (application_id) LEFT JOIN loan_types USING (loan_type_id) GROUP BY city,loan_type_id,loan_type_name ) SELECT DISTINCT c.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, loan_type_name AS most_applied_loan_type FROM loan_applications l LEFT JOIN customers c USING (customer_id) LEFT JOIN temp ON temp.city = c.city AND rk =1 GROUP BY city,loan_type_name ORDER BY city

贷款情况

输入

输出

问题信息

热门推荐

相关试题