with
	z as
		(select
			pf_code
			,sum(amt) as amt
		from
			drink_habits
		where
			gender_code in (2, 3)
			and category_code = 110
		group by
			1
		)
	,m as
		(select
			pf_code
			,sum(amt) as amt
		from
			drink_habits
		where
			gender_code in (2, 3)
			and category_code = 120
		group by
			1
		)

select
	p.pf_code as CODE
	,p.pf_name as NAME
	,round(cast(m.amt as real) * 100 / cast(z.amt as real), 1) as PERCENTAGE
from
	prefecture p
	join
		z
		on
			p.pf_code = z.pf_code
	join
		m
		on
			p.pf_code = m.pf_code
order by
	3 desc, 1 desc