WITH RATETABLE as (
    SELECT
        PF_CODE,
        ROUND(
            100.0 * INP_YES / (INP_YES + INP_NO + UNIDENTIFIED),
            1
        ) as RATE
    FROM
        HOSPITALIZATION
    GROUP BY
        PF_CODE
)
SELECT
    P.PF_CODE as 都道府県コード,
    P.PF_NAME as 都道府県名,
    R.RATE as 入院率
FROM
    RATETABLE as R
    INNER JOIN PREFECTURE as P on P.PF_CODE = R.PF_CODE
ORDER BY 
    R.RATE DESC,
    P.PF_CODE ASC