--【問題9】
WITH
BASE AS(
SELECT
	CHECKUP_DATE
,	MEMBER_CODE
,	WEIGHT
,	RANK () OVER (partition by MEMBER_CODE order by CHECKUP_DATE DESC) AS RANK
FROM
	HEALTH_CHECKUP
)
SELECT
	A.CHECKUP_DATE AS CK_DATE
,	A.MEMBER_CODE AS CODE
,	C.LAST_NAME || C.FIRST_NAME AS NAME
,	ROUND(A.WEIGHT - B.WEIGHT,1) AS CHG_WT
FROM
	BASE A
INNER JOIN
	BASE B
ON
	A.MEMBER_CODE = B.MEMBER_CODE
INNER JOIN
	MEMBER_MST C
ON
	A.MEMBER_CODE = C.MEMBER_CODE
WHERE
	A.RANK = 1
AND	B.RANK = 2
AND	(
		A.WEIGHT - B.WEIGHT > 4.9
	OR	A.WEIGHT - B.WEIGHT < -4.9
	)
ORDER BY
	A.WEIGHT - B.WEIGHT DESC
,	A.MEMBER_CODE DESC