SELECT
a.CODE,
a.NAME,
COUNT(a.CODE) AS CNT
FROM
(
SELECT
o.ORDER_DATE,
c.CUST_CODE AS CODE,
c.CUST_NAME AS NAME,
COUNT(o.ORDER_DATE) AS DATECNT
FROM
CUSTOMER c
LEFT JOIN ORDERS o ON o.CUST_CODE = c.CUST_CODE
WHERE
o.ORDER_DATE BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY c.CUST_CODE,o.ORDER_DATE
) a
GROUP BY a.CODE
ORDER BY CNT DESC,a.CODE DESC