with t as (
    select
        t.PF_CODE
        ,p.PF_NAME
        ,SUM(t.AMT) as total
    from
        DRINK_HABITS as t
    inner join PREFECTURE as p
        on t.PF_CODE = p.PF_CODE 
    group by
        t.PF_CODE
), d as (
    select
        PF_CODE
        ,SUM(AMT) as sum
    from
        DRINK_HABITS
    where
        CATEGORY_CODE = '120'
        and GENDER_CODE in ('2','3')
    group by
        PF_CODE
)

select
    t.PF_CODE as CODE
    ,t.PF_NAME as NAME
    ,round(cast(d.sum AS REAL) /cast(t.total AS REAL) ,2) as PERCENTAGE
from
    t
inner join d
    on t.PF_CODE = d.PF_CODE
order by
    PERCENTAGE desc
    ,t.PF_CODE desc