-- SELECT
--     A.MEMBER_CODE,
--     count(*)
-- FROM
--     HEALTH_CHECKUP AS A
--     INNER JOIN HEALTH_CHECKUP AS B
--     ON A.
-- GROUP BY
--     A.MEMBER_CODE

-- 最新の実施日 → CK_DATE
-- MEMBER_CODE → CODE
-- LAST_NAMEとFIRST_NAMEを連結して表示 → NAME
-- 変動した体重 → CHG_WT
-- 表示順

-- 変動した体重の降順
-- MEMBER_CODEの降順

SELECT
    B1.CHECKUP_DATE AS CK_DATE,
    B1.MEMBER_CODE AS CODE,
    MM.LAST_NAME||MM.FIRST_NAME AS NAME,
    round(B1.WEIGHT - B2.WEIGHT,3) AS CHG_WT
FROM
    MEMBER_MST AS MM
    INNER JOIN (
        SELECT
            row_number() over (partition by MEMBER_CODE order by CHECKUP_DATE desc) as rank,
            MEMBER_CODE,
            WEIGHT,
            CHECKUP_DATE
        FROM HEALTH_CHECKUP
    ) AS B1
    ON MM.MEMBER_CODE = B1.MEMBER_CODE AND B1.rank = 1
    INNER JOIN (
        SELECT
            row_number() over (partition by MEMBER_CODE order by CHECKUP_DATE desc) as rank,
            MEMBER_CODE,
            WEIGHT,
            CHECKUP_DATE
        FROM HEALTH_CHECKUP
    ) AS B2
    ON MM.MEMBER_CODE = B2.MEMBER_CODE AND B2.rank = 2
WHERE
    abs(CHG_WT) >= 5.0
ORDER BY
    MM.MEMBER_CODE