WITH T1 AS(
    SELECT
        MEMBER_CODE AS CODE,
        CHECKUP_DATE AS CK_DATE,
        WEIGHT,
        RANK() OVER(
            PARTITION BY MEMBER_CODE
            ORDER BY CHECKUP_DATE DESC
        ) AS RANK,
        COUNT() OVER(
            PARTITION BY MEMBER_CODE
        ) AS CNT
    FROM
        HEALTH_CHECKUP
), T2 AS(
    SELECT
        CODE,
        CK_DATE,
        WEIGHT,
        RANK
    FROM
        T1
    WHERE
        CNT >= 2
        AND RANK <= 2
), T3 AS(
    SELECT
        CODE,
        CK_DATE,
        RANK,
        WEIGHT - SUM(WEIGHT) OVER(
            PARTITION BY CODE
            ORDER BY RANK ASC
            ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
        ) AS CHG_WT
    FROM
        T2
), T4 AS(
    SELECT
        CODE,
        CK_DATE,
        CHG_WT
    FROM
        T3
    WHERE
        RANK = 1
), T5 AS(
    SELECT
        CK_DATE,
        T4.CODE,
        LAST_NAME || FIRST_NAME AS NAME,
        ROUND(CHG_WT, 1) AS CHG_WT
    FROM
        T4
    INNER JOIN
        MEMBER_MST
            ON T4.CODE = MEMBER_MST.MEMBER_CODE
    WHERE
        ABS(CHG_WT) >= 4.999 
    ORDER BY
        CHG_WT DESC,
        T4.CODE DESC
)
SELECT
    *
FROM
    T5