cinimex_creditdb_3.sql

WITH corp_payments AS (
	SELECT
		p.member_no,
		c.corp_no,
		c.corp_name,
		p.payment_amt
	FROM `member` m 
	JOIN payment p
		ON m.member_no = p.member_no 
	RIGHT JOIN corporation c
		ON m.corp_no = c.corp_no 
)
SELECT
	cp.corp_name,
	COUNT(cp.member_no) members_count,
	ROUND(AVG(cp.payment_amt), 4) payment_avg
FROM corp_payments cp
GROUP BY cp.corp_no
ORDER BY members_count DESC, payment_avg DESC;