题解 | 贷款情况

with cities as(
    select city,round(sum(loan_amount),2) as total_loan_amount,
    round(sum(loan_amount)/count(distinct c.customer_id),2) as average_loan_amount,
    round(count(distinct c.customer_id),2) as total_customers
    from customers c
    join loan_applications using(customer_id)
    group by city
),
type as(
    select city,loan_type_name,row_number() over(partition by city order by count(*) desc,l.loan_type_id) as rk
    from loan_applications
    join customers using(customer_id)
    join loan_application_types using(application_id)
    join loan_types l using(loan_type_id)
    group by city,loan_type_name,l.loan_type_id
)
select c.city,total_loan_amount,average_loan_amount,total_customers,loan_type_name as most_applied_loan_type
from cities c
join type t on c.city=t.city and rk=1
order by city