--受注日(ORDER_DATE)2023年9月1日から2023年9月30日
--顧客毎の受注件数、受注金額合計、１回あたりの平均受注金額
--受注件数が5件未満の顧客は対象外
--上位最大5件

WITH A AS(
SELECT O.CUST_CODE AS CODE,
C.CUST_NAME AS NAME,
COUNT(O.ORDER_NO) AS CNT,
SUM(O.ORDER_AMNT) AS TTL_AMT,
ROUND(AVG(O.ORDER_AMNT),0) AS AVG_AMT
FROM ORDERS AS O
JOIN CUSTOMER AS C
ON O.CUST_CODE = C.CUST_CODE
WHERE ORDER_DATE BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY C.CUST_CODE
ORDER BY CNT DESC,AVG_AMT DESC,CODE ASC)

SELECT *
FROM A
WHERE CNT >= 5
LIMIT 5;