--ORDER_DATE:2023年4月1日から6月30日
--ORDER_NOで結合
--商品ごとに受注数量(ORDER_QTY)を集計
--受注数>最新の受注日>商品コード(ITEM_CODE)の降順

WITH A AS(
SELECT ORDER_QTY,MAX(ORDER_DATE),ITEM_CODE,
ROW_NUMBER() OVER(ORDER BY ORDER_QTY DESC,MAX(ORDER_DATE) DESC,ITEM_CODE DESC) AS PRANK
FROM ORDERS_DTL
JOIN ORDERS
ON ORDERS_DTL.ORDER_NO = ORDERS.ORDER_NO
WHERE ORDERS.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY ITEM_CODE
),

B AS(
SELECT ITEM_CODE,PRANK
FROM A
),

C AS(
SELECT *
FROM ITEM
LEFT JOIN B
ON ITEM.ITEM_CODE = B.ITEM_CODE
ORDER BY ITEM.ITEM_CODE ASC
)

SELECT ITEM_CODE,ITEM_NAME,ITEM_TYPE,
STOCK_MANAGEMENT_TYPE,
TAX_TYPE,TAX_NO,SHORT_DESCRIPTION,
SALE_START_DATE,SALE_END_DATE,
DISCOUNT_PRICE_START_DATETIME,DISCOUNT_PRICE_END_DATETIME,
DELIVERY_TYPE_NO,RECOMMEND_ITEM_RANK,
IFNULL(PRANK, '0') AS ITEM_POPULAR_RANK,
UPDATE_USER_NAME,USER_UPDATE_DATETIME
FROM C;

--SELECT ifnull(列名,'0') FROM テーブル名;


--集計した受注数量が多い商品から順に1からの連番
--人気順位(ITEM_POPULAR_RANK)
--対象期間に受注が存在しない商品の人気順位は0