WITH JOINED AS (
    SELECT
        DH.CATEGORY_CODE,
        DH.PF_CODE,
        PF.PF_NAME,
        SUM(DH.AMT) AS SUM
    FROM
        DRINK_HABITS AS DH
        INNER JOIN PREFECTURE AS PF ON DH.PF_CODE = PF.PF_CODE
    WHERE
        GENDER_CODE != 1
    GROUP BY
        DH.PF_CODE,
        DH.CATEGORY_CODE
)
SELECT
    PF_CODE AS CODE,
    PF_NAME AS NAME,
    ROUND(
        CAST (
            SUM(
                CASE
                    WHEN CATEGORY_CODE = 120 THEN SUM
                    ELSE 0
                END
            ) AS REAL
        ) / CAST (
            SUM(
                CASE
                    WHEN CATEGORY_CODE = 110 THEN SUM
                    ELSE 0
                END
            ) AS REAL
        ) * 100.0,
        1
    ) AS PERCENTAGE
FROM
    JOINED
GROUP BY
    PF_CODE
ORDER BY
    `PERCENTAGE` DESC,
    PF_CODE DESC