with hoge2022 as (
    select
        A.AREA_CODE
        ,rank() OVER(order by A.AREA_CODE )as YRANK
        ,round( cast(H.FOOD_EXP AS REAL) / cast(H.CONSUMPTION_EXP AS REAL) ,4)  as ratio
    From AREA A
        LEFT JOIN (SELECT * FROM HOUSEHOLD_SURVEY WHERE SURVEY_YEAR=2022 ) H
        ON A.AREA_CODE = H.AREA_CODE
)
, hoge2017 as (
    select
        A.AREA_CODE
        ,rank() OVER(order by A.AREA_CODE )as YRANK
        ,round( cast(H.FOOD_EXP AS REAL) / cast(H.CONSUMPTION_EXP AS REAL) ,4)  as ratio
    From AREA A
        LEFT JOIN (SELECT * FROM HOUSEHOLD_SURVEY WHERE SURVEY_YEAR=2022 ) H
        ON A.AREA_CODE = H.AREA_CODE
)
, hoge2012 as (
    select
        A.AREA_CODE
        ,rank() OVER(order by A.AREA_CODE ) as YRANK
        ,round( cast(H.FOOD_EXP AS REAL) / cast(H.CONSUMPTION_EXP AS REAL) ,4)  as ratio
    From AREA A
        LEFT JOIN (SELECT * FROM HOUSEHOLD_SURVEY WHERE SURVEY_YEAR=2022 ) H
        ON A.AREA_CODE = H.AREA_CODE
)

SELECT
A.AREA_CODE AS CODE
,A.AREA_NAME AS NAME
, H22.YRANK AS 2022_RANK --2022年の順位を表示 → 2022_RANK
, H22.RATIO AS 2022_RATIO --2022年の比率に'%'を連結して表示 → 2022_RATIO
, H17.YRANK AS 2017_RANK --2017年の順位を表示 → 2017_RANK
, H17.RATIO AS 2017_RATIO --2017年の比率に'%'を連結して表示 → 2017_RATIO
, H12.YRANK AS 2012_RANK --2012年の順位を表示 → 2012_RANK
, H12.RATIO AS 2012_RATIO --2012年の比率に'%'を連結して表示 → 2012_RATIO
FROM
AREA A
LEFT JOIN (SELECT * FROM hoge2022 WHERE YRANK >=5) H22
    ON A.AREA_CODE = H22.AREA_CODE
LEFT JOIN (SELECT * FROM hoge2017 WHERE YRANK >=5) H17
    ON A.AREA_CODE = H17.AREA_CODE
LEFT JOIN (SELECT * FROM hoge2012 WHERE YRANK >=5)  H12
    ON A.AREA_CODE = H12.AREA_CODE