WITH counts AS (
    SELECT
        DRINK_HABITS.PF_CODE
        , PREFECTURE.PF_NAME
        , CATEGORY_CODE
        , SUM(AMT) AS counts
    FROM
        DRINK_HABITS
        INNER JOIN
        PREFECTURE
        ON
        DRINK_HABITS.PF_CODE = PREFECTURE.PF_CODE
    WHERE
        GENDER_CODE IN ('2', '3')
    GROUP BY
        1, 2, 3
)
, ratio AS (
SELECT
    PF_CODE
    , PF_NAME
    , MAX(CASE WHEN CATEGORY_CODE = '110' THEN counts ELSE NULL END) AS counts_110
    , MAX(CASE WHEN CATEGORY_CODE = '120' THEN counts ELSE NULL END) AS counts_120
FROM
    counts
GROUP BY
    1, 2
)
SELECT
    PF_CODE
    , PF_NAME
    , ROUND((CAST(counts_120 AS FLOAT) / counts_110)*100.0, 1) AS PERCENTAGE
FROM
    ratio
ORDER BY
    PERCENTAGE DESC
    , PF_CODE DESC
;