题解 | 贷款情况

WITH t0 AS (
	SELECT
		city,
		sum( loan_amount ) AS total_loan_amount,
		round( sum( loan_amount ) / count( DISTINCT customer_name ), 2 ) AS average_loan_amount,
		count( DISTINCT customer_name ) AS total_customers 
	FROM
		customers c
		LEFT JOIN loan_applications l ON l.customer_id = c.customer_id 
	GROUP BY
		city 
	) SELECT
	t0.city,
	total_loan_amount,
	average_loan_amount,
	total_customers,
	most_applied_loan_type 
FROM
	(
	SELECT
		city,
		loan_type_name AS most_applied_loan_type,
		count( l0.loan_type_id ) AS cnt,
		ROW_NUMBER() over ( PARTITION BY city ORDER BY count( l0.loan_type_id ) DESC, l1.loan_type_id ) AS rk 
	FROM
		loan_application_types l0
		LEFT JOIN loan_types l1 ON l0.loan_type_id = l1.loan_type_id
		JOIN loan_applications l3 ON l0.application_id = l3.application_id
		LEFT JOIN customers c ON l3.customer_id = c.customer_id 
	GROUP BY
		1,
		2,
		l1.loan_type_id
	) t
	JOIN t0 ON t.city = t0.city 
WHERE
	rk = 1 
ORDER BY
	1