题解 | 贷款情况

with a as (
    select city, sum(loan_amount) as total_loan_amount, round(sum(loan_amount)/count(distinct customer_id), 2) as average_loan_amount, count(distinct customer_id) as total_customers
    from loan_applications join loan_application_types using(application_id) join customers using(customer_id) join loan_types using(loan_type_id)
    group by city
),
b as (
    select city, loan_type_id, loan_type_name, count(loan_type_name) over(partition by city) as cnt 
    from loan_applications join loan_application_types using(application_id) join customers using(customer_id) join loan_types using(loan_type_id)
),
c as (
    select city, loan_type_id, loan_type_name, rank() over(partition by city order by cnt desc, loan_type_id) as rnk
    from b 
)
select distinct city, total_loan_amount, average_loan_amount, total_customers, loan_type_name as most_applied_loan_type
from a join c using(city)
where rnk = 1
order by city

首先平均贷款的数量是要按照distinct customer_id来计算，直接group by city然后average会有问题。其次是每个城市最多的贷款金额还是有点麻烦，我先用窗口函数计算数量（groupby当然也行），然后用窗口函数排序。还是比较麻烦的。这种题先把简单的列算出来，难的列放在后面写，再join