--【問題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 * 10 - B.WEIGHT * 10) / 10 ,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	(
		ROUND((A.WEIGHT * 10 - B.WEIGHT * 10) / 10 ,1) >= 5
	OR	ROUND((A.WEIGHT * 10 - B.WEIGHT * 10) / 10 ,1) <= -5
	)
ORDER BY
	ROUND((A.WEIGHT * 10 - B.WEIGHT * 10) / 10 ,1) DESC
,	A.MEMBER_CODE DESC