WITH OrderedItems AS (
  SELECT
    ITEM.ITEM_CODE,
    ITEM.ITEM_NAME,
    SUM(ORDERS_DTL.ORDER_QTY) AS TotalOrderQty,
    MAX(ORDERS.ORDER_DATE) AS LatestOrderDate
  FROM ITEM
  LEFT JOIN ORDERS_DTL ON ITEM.ITEM_CODE = ORDERS_DTL.ITEM_CODE
  LEFT JOIN ORDERS ON ORDERS_DTL.ORDER_NO = ORDERS.ORDER_NO
    AND ORDERS.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
  GROUP BY ITEM.ITEM_CODE
),
Ranking AS (
  SELECT
    ITEM_CODE,
    ITEM_NAME,
    TotalOrderQty,
    LatestOrderDate,
    CASE WHEN TotalOrderQty IS NULL OR TotalOrderQty = 0 THEN 0
         ELSE ROW_NUMBER() OVER (
           ORDER BY TotalOrderQty DESC, LatestOrderDate DESC, ITEM_CODE DESC
         )
    END AS ITEM_POPULAR_RANK
  FROM OrderedItems
)
SELECT ITEM_CODE,ITEM_NAME,ITEM_POPULAR_RANK FROM Ranking;