WITH A AS (SELECT ITEM_CODE, CASE WHEN SUM(ORDER_QTY) IS NULL THEN 0 ELSE RANK() OVER (ORDER BY SUM(ORDER_QTY) DESC, MAX(ORDER_DATE) DESC) END AS ITEM_POPULAR_RANK
FROM ITEM LEFT JOIN ORDERS_DTL USING (ITEM_CODE) LEFT JOIN (SELECT ORDER_NO, ORDER_DATE FROM ORDERS WHERE ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30') O USING (ORDER_NO)
GROUP BY ITEM_CODE)
UPDATE ITEM SET ITEM_POPULAR_RANK = (SELECT A.ITEM_POPULAR_RANK FROM A WHERE ITEM.ITEM_CODE = A.ITEM_CODE);