WITH SUMMARY AS (
    SELECT 
        MEMBER_CODE,
        MAX(CHECKUP_DATE)   OVER (PARTITION BY MEMBER_CODE) AS LATEST_DATE,
        LEAD(CHECKUP_DATE)  OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS BEFORE_DATE,
        FIRST_VALUE(WEIGHT) OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS LATEST_WEIGHT,
        LEAD(WEIGHT)        OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS BEFORE_WEIGHT
    FROM HEALTH_CHECKUP
),
TEMP AS (
    SELECT 
        SUMMARY.LATEST_DATE AS CK_DATE,
        MEMBER_CODE AS CODE,
        MEMBER_MST.LAST_NAME || MEMBER_MST.FIRST_NAME AS NAME,
        (CAST(SUMMARY.LATEST_WEIGHT*10 AS INT) 
            - CAST(SUMMARY.BEFORE_WEIGHT*10 AS INT))
        /CAST(10.0 AS NUMERIC) AS CHG_WT
    FROM SUMMARY
        INNER JOIN MEMBER_MST USING (MEMBER_CODE)
    WHERE
        BEFORE_DATE IS NOT NULL
        AND LATEST_DATE <> BEFORE_DATE
        AND LATEST_DATE = (
            SELECT MAX(CHECKUP_DATE)
            FROM HEALTH_CHECKUP
            WHERE SUMMARY.MEMBER_CODE = HEALTH_CHECKUP.MEMBER_CODE
        )
)
SELECT 
    CK_DATE,
    CODE,
    NAME,
    CHG_WT
FROM TEMP
WHERE
    ABS(CHG_WT) >= 5
ORDER BY
    CHG_WT DESC,
    CODE DESC
;