WITH TOTAL_AMT AS(
    SELECT
    PF_CODE AS CODE
    ,SUM(AMT) AS TOTAL_AMT
    FROM DRINK_HABITS
    WHERE GENDER_CODE IN ('2','3')
    GROUP BY PF_CODE
)

,EVERY_AMT AS(
    SELECT
    PF_CODE AS CODE
    ,SUM(AMT) AS EVERY_AMT
    FROM DRINK_HABITS
    WHERE CATEGORY_CODE = '120' AND GENDER_CODE IN ('2','3')
    GROUP BY PF_CODE
)


SELECT
    CODE
    ,PF_NAME AS NAME
    ,ROUND(CAST(EVERY_AMT AS REAL) / CAST(TOTAL_AMT AS REAL) * 100, 1) AS "PERCENTAGE"
FROM TOTAL_AMT NATURAL JOIN EVERY_AMT JOIN PREFECTURE ON CODE = PF_CODE
ORDER BY PERCENTAGE DESC, CODE DESC;