WITH tbl1 AS (
    SELECT
        CATEGORY_CODE,
        GENDER_CODE,
        PF_CODE,
        AMT
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN ('2', '3')
    ),
tbl2 AS (
    SELECT
        PF_CODE,
        SUM(AMT) AS total_amt
    FROM
        tbl1
    WHERE
        CATEGORY_CODE = '110'
    GROUP BY
        PF_CODE
    ),
tbl3 AS (
    SELECT
        PF_CODE,
        SUM(AMT) AS drink_amt
    FROM
        tbl1
    WHERE
        CATEGORY_CODE = '120'
    GROUP BY
        PF_CODE
    ),
tbl4 AS (
    SELECT 
        tbl3.PF_CODE,
        ROUND(100 * CAST(tbl3.drink_amt AS real)/CAST(tbl2.total_amt AS real),1)  AS PERCENTAGE
    FROM
        tbl2
    LEFT JOIN
        tbl3
        ON tbl2.PF_CODE = tbl3.PF_CODE
    )
SELECT
    tbl4.PF_CODE AS CODE,
    PREFECTURE.PF_NAME AS NAME,
    tbl4.PERCENTAGE AS PERCENTAGE
FROM
    tbl4
INNER JOIN
    PREFECTURE
    ON tbl4.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    tbl4.PERCENTAGE DESC, tbl4.PF_CODE