-- 最新の健康診断結果と直前の健康診断結果を取得するためのCTE（共通テーブル式）
WITH LatestCheckup AS (
    SELECT
        H1.MEMBER_CODE,
        H1.CHECKUP_DATE AS CK_DATE,
        H1.WEIGHT AS CURRENT_WEIGHT,
        LAG(H1.WEIGHT, 1) OVER (PARTITION BY H1.MEMBER_CODE ORDER BY H1.CHECKUP_DATE) AS PREVIOUS_WEIGHT
    FROM
        HEALTH_CHECKUP H1
),
-- 体重変動が5kg以上の会員をフィルタリングするCTE
WeightChange AS (
    SELECT
        LC.MEMBER_CODE,
        LC.CK_DATE,
        LC.CURRENT_WEIGHT,
        LC.PREVIOUS_WEIGHT,
        (LC.CURRENT_WEIGHT - LC.PREVIOUS_WEIGHT) AS CHG_WT
    FROM
        LatestCheckup LC
    WHERE
        LC.PREVIOUS_WEIGHT IS NOT NULL
        AND ABS(LC.CURRENT_WEIGHT - LC.PREVIOUS_WEIGHT) >= 5
)
-- 結果を取得して必要な情報を結合する
SELECT
    WC.CK_DATE AS CK_DATE,
    MM.MEMBER_CODE AS CODE,
    (MM.LAST_NAME || MM.FIRST_NAME) AS NAME,
    CASE
        WHEN ROUND(WC.CHG_WT, 1) = CAST(WC.CHG_WT AS INTEGER) THEN CAST(WC.CHG_WT AS INTEGER)
        ELSE ROUND(WC.CHG_WT, 1)
    END AS CHG_WT
FROM
    WeightChange WC
JOIN
    MEMBER_MST MM ON WC.MEMBER_CODE = MM.MEMBER_CODE
ORDER BY
    WC.CHG_WT DESC,
    MM.MEMBER_CODE DESC;