WITH Ranked_Checkups AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE,
        WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS rn
    FROM
        HEALTH_CHECKUP
),
Latest_Checkup AS (
    SELECT
        rc1.MEMBER_CODE,
        rc1.CHECKUP_DATE AS LATEST_DATE,
        rc1.WEIGHT AS LATEST_WEIGHT,
        rc2.CHECKUP_DATE AS PREVIOUS_DATE,
        rc2.WEIGHT AS PREVIOUS_WEIGHT
    FROM
        Ranked_Checkups rc1
    JOIN
        Ranked_Checkups rc2
    ON
        rc1.MEMBER_CODE = rc2.MEMBER_CODE
        AND rc2.rn = 2
    WHERE
        rc1.rn = 1
)
SELECT
    lc.LATEST_DATE AS CK_DATE,
    lc.MEMBER_CODE AS CODE,
    m.LAST_NAME || m.FIRST_NAME AS NAME,
    CASE
        WHEN ABS(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT) = ROUND(ABS(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT), 0) THEN CAST(ROUND(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT, 0) AS INTEGER)
        ELSE ROUND(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT, 1)
    END AS CHG_WT
FROM
    Latest_Checkup lc
JOIN
    MEMBER_MST m ON lc.MEMBER_CODE = m.MEMBER_CODE
WHERE
    lc.PREVIOUS_WEIGHT IS NOT NULL
    AND ABS(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT) >= 5
ORDER BY
    ABS(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT) DESC,
    lc.MEMBER_CODE DESC;