WITH TMP AS (
 SELECT RANK() OVER(PARTITION BY MEMBER_CODE
                        ORDER BY CHECKUP_DATE DESC) AS RNK
       ,CHECKUP_DATE
       ,MEMBER_CODE
       ,WEIGHT * 10 AS WEIGHT
       ,LEAD(WEIGHT) OVER(PARTITION BY MEMBER_CODE
                                ORDER BY CHECKUP_DATE DESC) * 10 AS BEFORE
   FROM HEALTH_CHECKUP
)
SELECT T.CHECKUP_DATE AS CK_DATE
      ,T.MEMBER_CODE AS CODE
      ,M.LAST_NAME || M.FIRST_NAME AS NAME
      ,ROUND((T.WEIGHT - T.BEFORE) / 10, 1) AS CHG_WT
  FROM TMP T
 INNER JOIN MEMBER_MST M
    ON T.MEMBER_CODE = M.MEMBER_CODE
 WHERE T.RNK = 1
   AND T.BEFORE IS NOT NULL
   AND ABS(T.BEFORE - T.WEIGHT) >= 50
 ORDER BY CHG_WT DESC
         ,CODE DESC