WITH TOTAL AS(
SELECT
	PF_CODE,
	SUM(AMT) AS TOTAL_AMT
FROM
	DRINK_HABITS
WHERE
	GENDER_CODE IN('2','3')
AND
	CATEGORY_CODE = '110' 
GROUP BY
	PF_CODE
),
EVERYDAY AS(
SELECT
	PF_CODE,
	SUM(AMT) AS EVERYDAY_AMT
FROM
	DRINK_HABITS
WHERE
	GENDER_CODE IN('2','3')
AND
	CATEGORY_CODE = '120' 
GROUP BY
	PF_CODE
)
SELECT
	T.PF_CODE AS CODE,
	P.PF_NAME AS NAME,
	ROUND(CAST(EVERYDAY_AMT as real) / CAST(TOTAL_AMT AS real) * 100,1) AS PERCENTAGE
FROM
	TOTAL AS T
INNER JOIN
	EVERYDAY AS E
ON
	T.PF_CODE = E.PF_CODE
INNER JOIN
	PREFECTURE AS P
ON
	T.PF_CODE = P.PF_CODE
ORDER BY
	PERCENTAGE DESC,
	T.PF_CODE DESC