WITH SUB1 AS(
    SELECT
        HC1.CHECKUP_DATE
        , HC1.MEMBER_CODE
        , HC1.WEIGHT
        
        --LAG関数で会員ごとに実施日が一つ前の体重を取得
        , 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
    
    --丸め誤差が発生するため、10倍して計算した結果を10で割る
    ,(SUB1.WEIGHT * 10 - SUB1.BEFORE_WT * 10) /10 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
        )
        
    AND ABS((SUB1.WEIGHT * 10 - SUB1.BEFORE_WT * 10) /10) >= 5
ORDER BY
    CHG_WT DESC
    , CODE DESC;