WITH LatestCheckups AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE,
        WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS rn
    FROM
        HEALTH_CHECKUP
),
RecentCheckups AS (
    SELECT
        MEMBER_CODE,
        MAX(CASE WHEN rn = 1 THEN WEIGHT END) AS LatestWeight,
        MAX(CASE WHEN rn = 2 THEN WEIGHT END) AS PreviousWeight,
        MAX(CASE WHEN rn = 1 THEN CHECKUP_DATE END) AS LatestDate,
        MAX(CASE WHEN rn = 2 THEN CHECKUP_DATE END) AS PreviousDate
    FROM
        LatestCheckups
    WHERE
        rn <= 2
    GROUP BY
        MEMBER_CODE
),
WeightChanges AS (
    SELECT
        MEMBER_CODE,
        LatestDate AS CK_DATE,
        ROUND(LatestWeight - PreviousWeight, 1) AS CHG_WT
    FROM
        RecentCheckups
    WHERE
        ABS(LatestWeight - PreviousWeight) >= 5
)
SELECT
    WC.CK_DATE,
    WC.MEMBER_CODE AS CODE,
    MM.LAST_NAME || ' ' || MM.FIRST_NAME AS NAME,
    CASE
        WHEN WC.CHG_WT = CAST(WC.CHG_WT AS INTEGER) THEN CAST(WC.CHG_WT AS INTEGER)
        ELSE WC.CHG_WT
    END AS CHG_WT
FROM
    WeightChanges WC
JOIN
    HEALTH_CHECKUP HC ON WC.MEMBER_CODE = HC.MEMBER_CODE AND WC.CK_DATE = HC.CHECKUP_DATE
JOIN
    MEMBER_MST MM ON WC.MEMBER_CODE = MM.MEMBER_CODE
ORDER BY
    WC.CHG_WT DESC,
    WC.MEMBER_CODE DESC;