WITH Latest_Checkup AS (
    SELECT
        h1.MEMBER_CODE,
        h1.CHECKUP_DATE AS LATEST_DATE,
        h1.WEIGHT AS LATEST_WEIGHT,
        (SELECT h2.CHECKUP_DATE 
         FROM HEALTH_CHECKUP h2 
         WHERE h2.MEMBER_CODE = h1.MEMBER_CODE 
         AND h2.CHECKUP_DATE < h1.CHECKUP_DATE 
         ORDER BY h2.CHECKUP_DATE DESC 
         LIMIT 1) AS PREVIOUS_DATE,
        (SELECT h2.WEIGHT 
         FROM HEALTH_CHECKUP h2 
         WHERE h2.MEMBER_CODE = h1.MEMBER_CODE 
         AND h2.CHECKUP_DATE < h1.CHECKUP_DATE 
         ORDER BY h2.CHECKUP_DATE DESC 
         LIMIT 1) AS PREVIOUS_WEIGHT
    FROM
        HEALTH_CHECKUP h1
    WHERE
        h1.CHECKUP_DATE = (
            SELECT MAX(h3.CHECKUP_DATE)
            FROM HEALTH_CHECKUP h3
            WHERE h3.MEMBER_CODE = h1.MEMBER_CODE
        )
),
Weight_Changes AS (
    SELECT
        lc.MEMBER_CODE,
        lc.LATEST_DATE AS CK_DATE,
        lc.LATEST_WEIGHT,
        lc.PREVIOUS_WEIGHT,
        (lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT) AS CHG_WT
    FROM
        Latest_Checkup lc
    WHERE
        lc.PREVIOUS_WEIGHT IS NOT NULL
        AND ABS(lc.LATEST_WEIGHT - lc.PREVIOUS_WEIGHT) >= 5
)
SELECT
    wc.CK_DATE,
    wc.MEMBER_CODE AS CODE,
    m.LAST_NAME || m.FIRST_NAME AS NAME,
    CASE
        WHEN wc.CHG_WT = ROUND(wc.CHG_WT, 0) THEN CAST(ROUND(wc.CHG_WT, 0) AS INTEGER)
        ELSE ROUND(wc.CHG_WT, 1)
    END AS CHG_WT
FROM
    Weight_Changes wc
    JOIN MEMBER_MST m ON wc.MEMBER_CODE = m.MEMBER_CODE
ORDER BY
    ABS(wc.CHG_WT) DESC,
    wc.MEMBER_CODE DESC;