WITH SUB1 AS ( 
  
    SELECT
        HC1.CHECKUP_DATE
        , HC1.MEMBER_CODE
        , HC1.WEIGHT
     
        , LAG(HC1.WEIGHT, 1) OVER ( 
            PARTITION BY
                HC1.MEMBER_CODE 
            ORDER BY
                HC1.CHECKUP_DATE
        ) AS BEFORE_WT 
    FROM
        HEALTH_CHECKUP AS HC1
) 
SELECT
    SUB1.CHECKUP_DATE AS CK_DATE
    , SUB1.MEMBER_CODE AS CODE
    , LAST_NAME || FIRST_NAME AS NAME
   
    , ROUND((SUB1.WEIGHT  - SUB1.BEFORE_WT ),1)  AS CHG_WT 
FROM
    SUB1 
   
    INNER JOIN MEMBER_MST AS MM 
        ON MM.MEMBER_CODE = SUB1.MEMBER_CODE 
WHERE
    SUB1.CHECKUP_DATE = ( 
        -- 会員毎に直近の実施日を取得して比較する
        SELECT
            MAX(CHECKUP_DATE) 
        FROM
            HEALTH_CHECKUP 
        WHERE
            MEMBER_CODE = SUB1.MEMBER_CODE
    ) 
    -- 計算結果の絶対値が5以上のデータを対象とする
    AND ABS(ROUND((SUB1.WEIGHT  - SUB1.BEFORE_WT ),1)) >= 5 
ORDER BY
    CHG_WT DESC
    , CODE DESC;