WITH DRINKER AS (
SELECT
    PF_CODE
    , TOTAL(AMT) AS DRINKER_COUNT
FROM
    DRINK_HABITS
WHERE
    CATEGORY_CODE = '120'
    AND GENDER_CODE IN ('2','3')
GROUP BY
    PF_CODE
) , RESIDENTS AS (
SELECT
    PF_CODE
    , TOTAL(AMT) AS RESIDENTS_COUNT
FROM
    DRINK_HABITS
GROUP BY
    PF_CODE
) 
SELECT
    D.PF_CODE AS CODE
    , PF_NAME AS NAME
    , ROUND(DRINKER_COUNT/RESIDENTS_COUNT*100 , 1) AS PERCENTAGE
FROM
    DRINKER D
    INNER JOIN
        RESIDENTS R ON D.PF_CODE = R.PF_CODE
    INNER JOIN
        PREFECTURE P ON D.PF_CODE = P.PF_CODE
ORDER BY
    PERCENTAGE DESC
    , D.PF_CODE DESC
;