with population as (
    select
        sum(case when gender_code in ('2', '3') and category_code = '110' then amt else 0 end) as total_population,
        sum(case when gender_code in ('2', '3') and category_code = '120' then amt else 0 end) as drink_population,
        pf_code
    from
        drink_habits
    group by
        pf_code
)
select
    population.pf_code as CODE,
    prefecture.pf_name as NAME,
    round(1.0 * population.drink_population / population.total_population * 100, 1) as PERCENTAGE
from
    population
inner join
    prefecture
    on prefecture.pf_code = population.pf_code
order by
    percentage desc,
    code desc
;