SELECT 
    chgs.CHECKUP_DATE AS CK_DATE,
    chgs.MEMBER_CODE AS CODE,
    MEMBER_MST.LAST_NAME || MEMBER_MST.FIRST_NAME AS NAME,
    -- 体重はkg単位で小数第一位まで登録されているものとする。また、変動した体重を算出する際は丸め誤差が発生しないように注意すること。ただし、小数点以下がゼロの場合は整数のみの表示とする。
    ABS(chgs._weight - chgs.prev_weight) / 10 AS CHG_WT
FROM (
    SELECT 
        *,
        WEIGHT * 10 AS _weight,
        LAG(WEIGHT) OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE) * 10 AS prev_weight,
        ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS id
    FROM
        HEALTH_CHECKUP
) AS chgs
INNER JOIN 
    MEMBER_MST
ON
    chgs.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
WHERE 
    chgs.id <= 1
    AND ABS(chgs._weight - chgs.prev_weight) >= 50
ORDER BY
    ABS(chgs.WEIGHT - chgs.prev_weight) DESC,
    chgs.MEMBER_CODE DESC