SELECT 
	CODE
	,NAME
	,CNT
	,TTL_AMT
	,AVG_AMT
FROM(
	SELECT
		CODE
		,NAME
		,CNT
		,TTL_AMT
		,AVG_AMT
		,RANK() OVER(ORDER BY CNT DESC,AVG_AMT DESC ,CODE) RANK
	FROM
	(
		SELECT
		    A.CUST_CODE CODE
		    ,A.CUST_NAME NAME
		    ,COUNT(B.ORDER_NO) CNT
		    ,SUM(B.ORDER_AMNT) TTL_AMT
		    ,ROUND(SUM(B.ORDER_AMNT) * 1.0/COUNT(B.ORDER_NO) * 1.0,0) AVG_AMT
		FROM
		    CUSTOMER A
		    ,ORDERS B
		WHERE
		 A.CUST_CODE = B.CUST_CODE
		AND
			B.ORDER_DATE BETWEEN '2023-09-01' AND '2023-09-30' 
		GROUP BY
		    A.CUST_CODE
		HAVING
			COUNT(B.ORDER_NO) > 4
	)
)
WHERE RANK <= 5
ORDER BY CNT DESC,AVG_AMT DESC ,CODE