WITH latest_two_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
        MEMBER_CODE,
        CHECKUP_DATE AS CK_DATE,
        WEIGHT AS LATEST_WEIGHT
    FROM
        latest_two_checkups
    WHERE
        rn = 1
),
previous_checkup AS (
    SELECT
        MEMBER_CODE,
        WEIGHT AS PREVIOUS_WEIGHT
    FROM
        latest_two_checkups
    WHERE
        rn = 2
),
weight_changes AS (
    SELECT
        lc.MEMBER_CODE,
        lc.CK_DATE,
        lc.LATEST_WEIGHT - pc.PREVIOUS_WEIGHT AS CHG_WT
    FROM
        latest_checkup lc
    JOIN
        previous_checkup pc ON lc.MEMBER_CODE = pc.MEMBER_CODE
    WHERE
        ABS(lc.LATEST_WEIGHT - pc.PREVIOUS_WEIGHT) >= 5
),
rounded_weight_changes AS (
    SELECT
        MEMBER_CODE,
        CK_DATE,
        ROUND(CHG_WT, 1) AS ROUNDED_CHG_WT
    FROM
        weight_changes
)
SELECT
    rwc.CK_DATE,
    rwc.MEMBER_CODE AS CODE,
    mn.LAST_NAME || mn.FIRST_NAME AS NAME,
    CASE
        WHEN rwc.ROUNDED_CHG_WT = FLOOR(rwc.ROUNDED_CHG_WT) THEN CAST(rwc.ROUNDED_CHG_WT AS INTEGER)
        ELSE rwc.ROUNDED_CHG_WT
    END AS CHG_WT
FROM
    rounded_weight_changes rwc
JOIN
    MEMBER_MST mn ON rwc.MEMBER_CODE = mn.MEMBER_CODE
ORDER BY
    CHG_WT DESC,
    CODE DESC;