-- 会員の健康診断結果を記録している健康診断テーブル(HEALTH_CHECKUP)より、
-- 会員毎に実施日(CHECKUP_DATE)が直近2回の健康診断結果で、
-- 今回の体重(WEIGHT)から前回の体重を減算した結果が、5kg以上変動した会員を表示しなさい。
-- 体重はkg単位で小数第一位まで登録されているものとする
-- また、変動した体重を算出する際は丸め誤差が発生しないように注意すること。
-- ただし、小数点以下がゼロの場合は整数のみの表示とする。
-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)
-- 最新の実施日 → CK_DATE
-- MEMBER_CODE → CODE
-- LAST_NAMEとFIRST_NAMEを連結して表示 → NAME
-- 変動した体重 → CHG_WT
-- 表示順
-- 変動した体重の降順
-- MEMBER_CODEの降順
WITH dif_calc AS (
    SELECT
        CHECKUP_DATE
        , MEMBER_CODE
        , WEIGHT
        , CAST(WEIGHT AS NUMERIC) - CAST((LAG(WEIGHT, 1) OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE ASC)) AS NUMERIC) AS DIFF1
        , LAST_VALUE(CHECKUP_DATE) OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE ASC) AS LAST_DATE
    FROM
        HEALTH_CHECKUP
)
SELECT
    CHECKUP_DATE AS CK_DATE
    , dif_calc.MEMBER_CODE AS CODE
    , mst.LAST_NAME || mst.FIRST_NAME AS NAME
    , ROUND(DIFF1, 1) AS CHG_WT
FROM
    dif_calc
LEFT JOIN
    MEMBER_MST AS mst
ON dif_calc.MEMBER_CODE = mst.MEMBER_CODE
WHERE
    (ABS(DIFF1) > 5)
    AND
    (CHECKUP_DATE = LAST_DATE)
ORDER BY
    CHG_WT DESC, CODE ASC
;