WITH RankedCheckups AS (
    SELECT
        hc.MEMBER_CODE,
        hc.CHECKUP_DATE,
        hc.WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY hc.MEMBER_CODE ORDER BY hc.CHECKUP_DATE DESC) AS rnk
    FROM
        HEALTH_CHECKUP hc
),
LatestCheckups AS (
    SELECT
        a.MEMBER_CODE,
        a.CHECKUP_DATE AS Latest_Date,
        a.WEIGHT AS Latest_Weight,
        b.CHECKUP_DATE AS Previous_Date,
        b.WEIGHT AS Previous_Weight
    FROM
        RankedCheckups a
        JOIN RankedCheckups b ON a.MEMBER_CODE = b.MEMBER_CODE AND b.rnk = 2
    WHERE
        a.rnk = 1
),
WeightChanges AS (
    SELECT
        lc.MEMBER_CODE,
        lc.Latest_Date AS CK_DATE,
        lc.Latest_Weight - lc.Previous_Weight AS CHG_WT
    FROM
        LatestCheckups lc
    WHERE
        ABS(lc.Latest_Weight - lc.Previous_Weight) >= 5
)
SELECT
    wc.CK_DATE,
    mm.MEMBER_CODE AS CODE,
    mm.LAST_NAME || mm.FIRST_NAME AS NAME,
    CASE
        WHEN wc.CHG_WT = ROUND(wc.CHG_WT) THEN CAST(wc.CHG_WT AS INTEGER)
        ELSE wc.CHG_WT
    END AS CHG_WT
FROM
    WeightChanges wc
    JOIN MEMBER_MST mm ON wc.MEMBER_CODE = mm.MEMBER_CODE
ORDER BY
    wc.CHG_WT DESC,
    mm.MEMBER_CODE DESC;