with DRINK_HABITS_total as(
    select
        PF_CODE
        ,CATEGORY_CODE
        ,sum(AMT) as total

    from
        DRINK_HABITS
        
    where
        GENDER_CODE in (2,3)
        and CATEGORY_CODE in (110)

    group by
        PF_CODE
        ,CATEGORY_CODE
)

,DRINK_HABITS_drink as(
    select
        PF_CODE
        ,CATEGORY_CODE
        ,sum(AMT) as drink_sum

    from
        DRINK_HABITS
        
    where
        GENDER_CODE in (2,3)
        and CATEGORY_CODE in (120)

    group by
        PF_CODE
        ,CATEGORY_CODE
)


select
    A.PF_CODE as CODE
    ,PR.PF_NAME as NAME
    ,round(cast(B.drink_sum as real)/cast(A.total as real) *100,1) as PERCENTAGE

from
    DRINK_HABITS_total as A

inner join
    DRINK_HABITS_drink as B
    on A.PF_CODE = B.PF_CODE

    ,PREFECTURE as PR
    on A.PF_CODE = PR.PF_CODE

order by
    PERCENTAGE desc
    ,CODE desc

;