with LAST_CHKUP as (
select
    MEMBER_CODE
    ,max(CHECKUP_DATE) as CHECKUP_DATE
from
    HEALTH_CHECKUP
group by 1
)

, WEIGHT_DIFF as (
select
    CHECKUP_DATE
    ,MEMBER_CODE
    ,WEIGHT
    ,round(0.0 + WEIGHT
    - lag(WEIGHT) over (partition by MEMBER_CODE 
        order by CHECKUP_DATE), 1) as CHG_WT
from
    HEALTH_CHECKUP
)
select
    W.CHECKUP_DATE as CK_DATE
    ,W.MEMBER_CODE as CODE
    ,M.LAST_NAME || M.FIRST_NAME as NAME
    ,W.CHG_WT
from 
    WEIGHT_DIFF W 
    inner join 
        LAST_CHKUP L
        on 
            W.MEMBER_CODE=L.MEMBER_CODE
            and W.CHECKUP_DATE=L.CHECKUP_DATE
    left outer join 
        MEMBER_MST as M
        on
            W.MEMBER_CODE=M.MEMBER_CODE
where
    abs(CHG_WT)>=5
order by 4 desc, 2 desc