cinimex_creditdb_2.sql

WITH charge_by_cat_prov AS (
	SELECT 
		p.provider_no, 
		p.provider_name, 
		ca.category_no, 
		ca.category_desc, 
		ch.charge_amt 
	FROM charge ch
	JOIN provider p
		ON ch.provider_no = p.provider_no 
	JOIN category ca
		ON ch.category_no = ca.category_no 
), grouped AS (
	SELECT 
		c.category_no,
		c.category_desc,
		c.provider_name,
		SUM(c.charge_amt) summary_charge,
		ROW_NUMBER() OVER (PARTITION BY category_no ORDER BY summary_charge DESC) category_provider_rating
	FROM charge_by_cat_prov c
	GROUP BY c.category_no, c.provider_name
	ORDER BY c.category_no, category_provider_rating
)
SELECT 
	g.category_desc,
	g.provider_name,
	g.summary_charge
FROM grouped g
WHERE g.category_provider_rating <= 10;