with order_join AS (
    select *
    from customer c
    left outer join orders o
        on c.CUST_CODE = o.CUST_CODE
    where CAST(o.order_date AS timestamp) 
        BETWEEN CAST('2023-09-01' AS timestamp)
        AND CAST('2023-09-30' AS timestamp)
    )
, order_count AS(
    select c.CUST_CODE
    from customer c
    left outer join orders o
        on c.CUST_CODE = o.CUST_CODE
    group by c.CUST_CODE
    having COUNT(1) >= 5
    )
select o.CUST_CODE AS CODE
    , CUST_NAME AS NAME
    , COUNT(1) AS CNT
    , SUM(ORDER_AMNT) AS TTL_AMT
    , round(CAST(SUM(ORDER_AMNT) AS DOUBLE) / CAST(COUNT(1) AS DOUBLE), 0) AS AVG_AMT
from order_join o
inner join order_count c
    on o.CUST_CODE = c.CUST_CODE
group by o.CUST_CODE, o.CUST_NAME
order by CNT desc
    , AVG_AMT desc
    , CODE;