-- Step 1: 直近2回の健康診断データを取得
WITH Latest_Two_Checkups AS (
    SELECT
        MEMBER_CODE,
        CHECKUP_DATE,
        WEIGHT,
        ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS rn
    FROM
        HEALTH_CHECKUP
),

-- Step 2: 最新と前回の体重データを結合
Recent_Checkups AS (
    SELECT
        l1.MEMBER_CODE,
        l1.CHECKUP_DATE AS CK_DATE,
        l1.WEIGHT AS LATEST_WEIGHT,
        l2.CHECKUP_DATE AS PREV_DATE,
        l2.WEIGHT AS PREVIOUS_WEIGHT,
        (l1.WEIGHT - l2.WEIGHT) AS CHG_WT
    FROM
        Latest_Two_Checkups l1
    JOIN
        Latest_Two_Checkups l2 ON l1.MEMBER_CODE = l2.MEMBER_CODE AND l1.rn = 1 AND l2.rn = 2
),

-- Step 3: 必要なデータを選択
Filtered_Checkups AS (
    SELECT
        rc.CK_DATE,
        rc.MEMBER_CODE AS CODE,
        rc.CHG_WT
    FROM
        Recent_Checkups rc
    WHERE
        ABS(rc.CHG_WT) >= 5
)

-- Step 4: 会員の名前を連結して結果を表示
SELECT
    fc.CK_DATE AS CK_DATE,
    fc.CODE AS CODE,
    m.LAST_NAME || m.FIRST_NAME AS NAME,
    CASE
        WHEN ROUND(fc.CHG_WT, 1) = ROUND(fc.CHG_WT) THEN CAST(ROUND(fc.CHG_WT) AS INTEGER)
        ELSE ROUND(fc.CHG_WT, 1)
    END AS CHG_WT
FROM
    Filtered_Checkups fc
JOIN
    MEMBER_MST m ON fc.CODE = m.MEMBER_CODE
ORDER BY
    CHG_WT DESC,
    CODE DESC;