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 
    where
        CATEGORY_CODE = '110'
    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)) *100 ,1) as PERCENTAGE
from
    t
inner join d
    on t.PF_CODE = d.PF_CODE
order by
    PERCENTAGE desc
    ,t.PF_CODE desc