WITH RECENT_CHECKUPS AS (
    SELECT
        hc.MEMBER_CODE,
        hc.CHECKUP_DATE,
        hc.WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY hc.MEMBER_CODE ORDER BY hc.CHECKUP_DATE DESC) AS RN
    FROM
        HEALTH_CHECKUP hc
),
LATEST_TWO_CHECKUPS AS (
    SELECT
        rc.MEMBER_CODE,
        MAX(CASE WHEN rc.RN = 1 THEN rc.CHECKUP_DATE END) AS LATEST_DATE,
        MAX(CASE WHEN rc.RN = 1 THEN rc.WEIGHT END) AS LATEST_WEIGHT,
        MAX(CASE WHEN rc.RN = 2 THEN rc.WEIGHT END) AS PREV_WEIGHT
    FROM
        RECENT_CHECKUPS rc
    WHERE
        rc.RN <= 2
    GROUP BY
        rc.MEMBER_CODE
)
SELECT
    ltc.LATEST_DATE AS CK_DATE,
    ltc.MEMBER_CODE AS CODE,
    m.LAST_NAME || m.FIRST_NAME AS NAME,
    ROUND(ltc.LATEST_WEIGHT - ltc.PREV_WEIGHT, 1) AS CHG_WT
FROM
    LATEST_TWO_CHECKUPS ltc
JOIN
    MEMBER_MST m ON ltc.MEMBER_CODE = m.MEMBER_CODE
WHERE
    ABS(ltc.LATEST_WEIGHT - ltc.PREV_WEIGHT) >= 4.99999
ORDER BY
    CHG_WT DESC,
    CODE DESC;