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
)

SELECT 
    SUMMARY.LATEST_DATE AS CK_DATE,
    MEMBER_CODE AS CODE,
    MEMBER_MST.LAST_NAME || MEMBER_MST.FIRST_NAME AS NAME,
    (SUMMARY.LATEST_WEIGHT*10 - SUMMARY.BEFORE_WEIGHT*10)/10 AS CHG_WT
FROM SUMMARY
    LEFT OUTER JOIN MEMBER_MST USING (MEMBER_CODE)
WHERE
    ABS(CHG_WT) >= 5
ORDER BY
    CHG_WT DESC,
    CODE DESC
;