--CUST_CODE → CODE
--CUST_NAME → NAME
--顧客毎の受注件数 → CNT
--顧客毎の受注金額合計 → TTL_AMT
--顧客毎の１回あたりの平均受注金額 → AVG_AMT
select 
    OO.CODE,
    --OO.EMP_CODE,
    CUSTOMER.CUST_NAME AS NAME,
    OO.CNT,
    OO.TTL_AMT,
    round(OO.AVG_AMT) AS AVG_AMT
FROM 
    (select 
        CUST_CODE AS CODE,
        EMP_CODE,
        COUNT(*) AS CNT,
        SUM(ORDER_AMNT) AS TTL_AMT,
        AVG(ORDER_AMNT) AS AVG_AMT
    FROM ORDERS
    WHERE ORDER_DATE BETWEEN "2023-09-01" AND "2023-09-30"
    GROUP BY CUST_CODE
    ) AS OO
INNER JOIN CUSTOMER ON OO.CODE = CUSTOMER.CUST_CODE
WHERE CNT >= 5
--表示項目CNTの降順
--表示項目AVG_AMTの降順
--表示項目CODEの昇順
ORDER BY CNT DESC, AVG_AMT DESC, CODE ASC
LIMIT 5
--2023年9月1日から2023年9月30日