with
total_people as(
select
    PF_CODE, sum(AMT) as AMT
from
    DRINK_HABITS
where
    CATEGORY_CODE = 110
    and GENDER_CODE != 1
group by
    PF_CODE
)
, drink_everyday as(
select
    PF_CODE, sum(AMT) as AMT
from
    DRINK_HABITS
where
    CATEGORY_CODE = 120
    and GENDER_CODE != 1
group by
    PF_CODE
)
select
    pf.PF_CODE as CODE
    , PF_NAME as NAME
    , round(cast(de.AMT as real) / cast(tp.AMT as real) * 100, 1) as PERCENTAGE
from
    PREFECTURE as pf
    join total_people as tp
    on pf.PF_CODE = tp.PF_CODE
    join drink_everyday as de
    on pf.PF_CODE = de.PF_CODE
order by
    PERCENTAGE desc, CODE desc