-- 最近の2回の健康診断データを抽出し、体重の変動を計算するサブクエリ
WITH RecentCheckups AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE,
        WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS RowNum
    FROM
        HEALTH_CHECKUP
),
WeightChanges AS (
    SELECT
        r1.MEMBER_CODE,
        r1.CHECKUP_DATE AS CK_DATE,
        r1.WEIGHT AS CurrentWeight,
        r2.WEIGHT AS PreviousWeight,
        CAST(r1.WEIGHT - r2.WEIGHT AS REAL) AS WeightChange
    FROM
        RecentCheckups r1
    JOIN
        RecentCheckups r2 ON r1.MEMBER_CODE = r2.MEMBER_CODE AND r1.RowNum = 1 AND r2.RowNum = 2
    WHERE
        ABS(r1.WEIGHT - r2.WEIGHT) >= 5.0
)

-- 最終的な結果を表示
SELECT
    w.CK_DATE,
    w.MEMBER_CODE AS CODE,
    (m.LAST_NAME || ' ' || m.FIRST_NAME) AS NAME,
    CASE
        WHEN w.WeightChange = CAST(w.WeightChange AS INTEGER) THEN CAST(w.WeightChange AS INTEGER)
        ELSE ROUND(w.WeightChange, 1)
    END AS CHG_WT
FROM
    WeightChanges w
JOIN
    MEMBER_MST m ON w.MEMBER_CODE = m.MEMBER_CODE
ORDER BY
    w.WeightChange DESC, w.MEMBER_CODE DESC;