--商品ごとの受注数を求める
WITH QTY AS(
SELECT
    OD.ITEM_CODE
    ,I.ITEM_NAME
    ,COUNT(O.ORDER_NO) AS ORDER_QTY
    ,MAX(O.ORDER_DATE) AS MAX_DATE
FROM
    ORDERS AS O
JOIN
    ORDERS_DTL AS OD
ON
    O.ORDER_NO = OD.ORDER_NO
JOIN
    ITEM AS I
ON
    I.ITEM_CODE = OD.ITEM_CODE
WHERE
    O.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY
    OD.ITEM_CODE
    ,I.ITEM_NAME
ORDER BY
    OD.ITEM_CODE
)
--期間内に受注がない商品も表示させる
,ALL_ITEM AS(
SELECT
    I.ITEM_CODE
    ,I.ITEM_NAME
    ,Q.ORDER_QTY
    ,Q.MAX_DATE
FROM
    ITEM AS I
LEFT JOIN
    QTY AS Q
ON
    I.ITEM_CODE = Q.ITEM_CODE
)
--RANKを出す
SELECT
    ITEM_CODE
    ,ITEM_NAME
    ,CASE WHEN ORDER_QTY >0 THEN
        RANK()
            OVER(ORDER BY ORDER_QTY DESC,MAX_DATE DESC,ITEM_CODE DESC)
        ELSE 0 END AS ITEM_POPULAR_RANK
FROM
    ALL_ITEM