with t as (
    select city, a.customer_id, loan_amount, d.loan_type_id, loan_type_name,
    count(1) over (partition by city, loan_type_name) as cnt
    from loan_applications a
    join customers b on a.customer_id=b.customer_id
    join loan_application_types c on a.application_id=c.application_id
    join loan_types d on c.loan_type_id=d.loan_type_id
), t1 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 t
    group by city
), t2 as (
    select distinct city, loan_type_name
    from (
        select *, row_number() over (partition by city order by cnt desc, loan_type_id) as rn
        from t
    ) temp2
    where rn=1
)
select t1.city, total_loan_amount, average_loan_amount, total_customers, loan_type_name as most_applied_loan_type
from t1 join t2 on t1.city=t2.city
order by t1.city