WITH LatestCheckup AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE AS LATEST_DATE,
        WEIGHT AS LATEST_WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS rn
    FROM
        HEALTH_CHECKUP
),
PreviousCheckup AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE AS PREVIOUS_DATE,
        WEIGHT AS PREVIOUS_WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS rn
    FROM
        HEALTH_CHECKUP
)
SELECT
    lc.LATEST_DATE AS CK_DATE,
    m.MEMBER_CODE AS CODE,
    m.LAST_NAME || ' ' || m.FIRST_NAME AS NAME,
    ROUND((lc.LATEST_WEIGHT - pc.PREVIOUS_WEIGHT), 1) AS CHG_WT
FROM
    LatestCheckup lc
JOIN
    PreviousCheckup pc ON lc.MEMBER_CODE = pc.MEMBER_CODE AND lc.rn = 1 AND pc.rn = 2
JOIN
    MEMBER_MST m ON lc.MEMBER_CODE = m.MEMBER_CODE
WHERE
    lc.LATEST_DATE >= '2023-05-01' AND lc.LATEST_DATE <= '2024-05-31'
    AND ABS(lc.LATEST_WEIGHT - pc.PREVIOUS_WEIGHT) >= 5
ORDER BY
    ABS(ROUND((lc.LATEST_WEIGHT - pc.PREVIOUS_WEIGHT), 1)) DESC,
    m.MEMBER_CODE DESC;