with drink_everyday as(
    select 
        PF_CODE, 
        sum(AMT) as amt
    from DRINK_HABITS
    where CATEGORY_CODE = '120' and GENDER_CODE in (2, 3)
    group by PF_CODE
), 
    drink_all as(
    select 
        PF_CODE, 
        sum(AMT) as amt
    from DRINK_HABITS
    where CATEGORY_CODE = '110' and GENDER_CODE in (2, 3)
    group by PF_CODE
) 
select 
    p.PF_CODE as CODE, 
    p.PF_NAME as NAME, 
    round(100 * cast(de.amt as real) / cast(da.amt as real) , 1) as PERCENTAGE
from drink_everyday as de
    inner join drink_all as da on de.PF_CODE = da.PF_CODE
    inner join PREFECTURE as p on de.PF_CODE = p.PF_CODE
order by PERCENTAGE desc, code desc
;