WITH TOTAL AS(
    SELECT
        PF_CODE,
        SUM(AMT) AS TOTAL_AMT
    FROM DRINK_HABITS
    WHERE
        CATEGORY_CODE='110'
        AND GENDER_CODE IN('2','3')
    GROUP BY PF_CODE
    ),DRINK AS(
    SELECT
        PF_CODE,
        SUM(AMT) AS DRINK_AMT
    FROM DRINK_HABITS
    WHERE
        CATEGORY_CODE='120'
        AND GENDER_CODE IN('2','3')
    GROUP BY PF_CODE
    )
    
SELECT
    TOTAL.PF_CODE AS CODE,
    PF_NAME AS NAME,
    ROUND(CAST(DRINK_AMT AS REAL)/CAST(TOTAL_AMT AS REAL)*100,1) AS PERCENTAGE
FROM
    TOTAL INNER JOIN PREFECTURE USING(PF_CODE)
    INNER JOIN DRINK USING(PF_CODE)
ORDER BY
    PERCENTAGE DESC,
    CODE DESC
;