贷款情况__牛客网

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

【原始表】
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 t1 as ( select city,sum(loan_amount) as total_loan_amount, round(sum(loan_amount)/count(distinct x.customer_id),2) as average_loan_amount, count(distinct x.customer_id) as total_customers from customers x join loan_applications y using(customer_id) group by 1 ),t2 as ( select x.application_id,x.customer_id,z.loan_type_id,z.loan_type_name,city from loan_applications x join loan_application_types y using(application_id) join loan_types z on y.loan_type_id=z.loan_type_id join customers a on x.customer_id=a.customer_id ),t3 as ( select city,loan_type_name from (select city,loan_type_name,row_number() over(partition by city order by count(*) desc,loan_type_id) as rk from t2 group by city,loan_type_name,loan_type_id) e where rk=1 ) select t1.*,t3.loan_type_name as most_applied_loan_type from t1 join t3 using(city) order by 1

with city_statistic as ( select c.city, lt.loan_type_name, row_number() over (partition by city order by count(distinct la.customer_id) desc,any_value(lt.loan_type_id) ) rn from loan_applications la join customers c on la.customer_id = c.customer_id join loan_application_types lat on la.application_id = lat.application_id join loan_types lt on lat.loan_type_id = lt.loan_type_id … join loan_application_types lat on la.application_id = lat.application_id join loan_types lt on lat.loan_type_id = lt.loan_type_id join city_statistic cs on cs.city = c.city where rn = 1 group by c.city

贷款情况

输入

输出

问题信息

热门推荐

相关试题