with SUB as(
    select
        MEMBER_CODE
        ,CHECKUP_DATE
        ,rank() over(partition by MEMBER_CODE order by CHECKUP_DATE desc)
            as HIST_NO
        ,WEIGHT
    from
        HEALTH_CHECKUP
    order by
        MEMBER_CODE
        ,CHECKUP_DATE DESC
    ),
    SUB2 as(
    select
        max(CHECKUP_DATE) as CK_DATE
        ,MEMBER_CODE
        ,max(case when HIST_NO=1 then WEIGHT else Null end) as WEIGHT_1
        ,max(case when HIST_NO=2 then WEIGHT else Null end) as WEIGHT_2
    from
        SUB
    where
        HIST_NO <= 2
    group by
        MEMBER_CODE
    order by
        max(CHECKUP_DATE),MEMBER_CODE
    )
select
    CK_DATE
    ,SUB2.MEMBER_CODE as CODE
    ,LAST_NAME || FIRST_NAME as NAME
    ,round(WEIGHT_1 - WEIGHT_2,1) as CHG_WT
from
    SUB2
    inner join MEMBER_MST as MM
       on SUB2.MEMBER_CODE=MM.MEMBER_CODE
where
    abs(round(WEIGHT_1 - WEIGHT_2,1)) >= 5
order by
    CHG_WT desc
    ,CODE desc
    ;