WITH CheckupRanked AS (
    SELECT 
        MEMBER_CODE,
        CHECKUP_DATE,
        WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS RN
    FROM 
        HEALTH_CHECKUP
)
,
LatestCheckups AS (
    SELECT 
        C1.MEMBER_CODE,
        C1.CHECKUP_DATE AS LATEST_DATE,
        C1.WEIGHT AS LATEST_WEIGHT,
        C2.CHECKUP_DATE AS PREV_DATE,
        C2.WEIGHT AS PREV_WEIGHT
    FROM 
        CheckupRanked C1
    JOIN 
        CheckupRanked C2
    ON 
        C1.MEMBER_CODE = C2.MEMBER_CODE
        AND C1.RN = 1
        AND C2.RN = 2
)

SELECT 
    L.LATEST_DATE AS CK_DATE,
    M.MEMBER_CODE AS CODE,
    M.LAST_NAME || M.FIRST_NAME AS NAME,
    CASE 
        WHEN (L.LATEST_WEIGHT - L.PREV_WEIGHT) = CAST((L.LATEST_WEIGHT - L.PREV_WEIGHT) AS INTEGER)
        THEN CAST((L.LATEST_WEIGHT - L.PREV_WEIGHT) AS INTEGER)
        ELSE ROUND(L.LATEST_WEIGHT - L.PREV_WEIGHT, 1)
    END AS CHG_WT
FROM 
    LatestCheckups L
JOIN 
    MEMBER_MST M
ON 
    L.MEMBER_CODE = M.MEMBER_CODE
WHERE 
    ROUND(ABS(L.LATEST_WEIGHT - L.PREV_WEIGHT),2) >= 5.0
ORDER BY 
    CHG_WT DESC,
    CODE DESC;