WITH SUB AS(
    SELECT
        PF_CODE,
        CATEGORY_CODE,
        SUM(AMT) AS SUM_AMT
    FROM
        DRINK_HABITS
    WHERE
        CATEGORY_CODE IN ('110','120')
        AND GENDER_CODE IN ('2','3')
    GROUP BY
        PF_CODE, CATEGORY_CODE
    ),
    SUB2 AS(
    SELECT
        PF_CODE,
        MAX(CASE WHEN CATEGORY_CODE='120' THEN SUM_AMT ELSE 0 END) AS DRINKING_AMT,
        MAX(CASE WHEN CATEGORY_CODE='110' THEN SUM_AMT ELSE 0 END) AS TOTAL_AMT
    FROM
        SUB
    GROUP BY
        PF_CODE
    )
SELECT 
    SUB2.PF_CODE AS CODE,
    PF_NAME AS NAME,
    ROUND(
        CAST(DRINKING_AMT AS REAL)/CAST(TOTAL_AMT AS REAL)*100
        ,1) AS PERCENTAGE
FROM
    SUB2
    INNER JOIN PREFECTURE AS PF
        ON PF.PF_CODE=SUB2.PF_CODE
ORDER BY
    PERCENTAGE DESC,
    CODE DESC
    ;