WITH daily_drinkers AS (
  SELECT
    PF_CODE,
    SUM(AMT) AS daily_drinkers_amt
  FROM DRINK_HABITS
  WHERE CATEGORY_CODE = '120' AND (GENDER_CODE = '2' OR GENDER_CODE = '3')
  GROUP BY PF_CODE
),
total_surveyed AS (
  SELECT
    PF_CODE,
    SUM(AMT) AS total_surveyed_amt
  FROM DRINK_HABITS
  WHERE CATEGORY_CODE = '110' AND (GENDER_CODE = '2' OR GENDER_CODE = '3')
  GROUP BY PF_CODE
),
combined_data AS (
  SELECT
    p.PF_CODE,
    p.PF_NAME,
    d.daily_drinkers_amt,
    t.total_surveyed_amt,
    (d.daily_drinkers_amt * 100.0 / t.total_surveyed_amt) AS drinking_rate
  FROM PREFECTURE p
  JOIN daily_drinkers d ON p.PF_CODE = d.PF_CODE
  JOIN total_surveyed t ON p.PF_CODE = t.PF_CODE
)
SELECT
  PF_CODE AS CODE,
  PF_NAME AS NAME,
  ROUND(drinking_rate, 1) AS PERCENTAGE
FROM combined_data
ORDER BY PERCENTAGE DESC, CODE DESC;