--各商品の売上金額の集計
WITH SUM_ITEM AS (
    SELECT
        SALES_DTL.ITEM_CODE AS CODE,
        SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS SAL_AMT
    FROM
        SALES_DTL
    INNER JOIN
        SALES ON SALES.SALES_NO == SALES_DTL.SALES_NO
    WHERE
        SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-31'
    GROUP BY
        SALES_DTL.ITEM_CODE
),
--売上金額の累積計算
RUISEKI AS (
    SELECT
        SUM_ITEM.CODE,
        SUM_ITEM.SAL_AMT,
        SUM(SUM_ITEM.SAL_AMT) OVER (ORDER BY SUM_ITEM.SAL_AMT DESC) AS CML_AMT
    FROM
        SUM_ITEM
),
--合計売上金額の計算
TOTAL_SALES AS (
    SELECT
        SUM(SALES_AMT) AS TOTAL
    FROM
        SALES
    WHERE 
        SALES_DATE BETWEEN '2023-06-01' AND '2023-06-31'
)
-- 売上構成比、類型構成比、RANKの計算
SELECT
    CODE,
    ITEM_NAME AS NAME,
    SAL_AMT,
    CML_AMT,
    ROUND((100.0 * SAL_AMT / TS.TOTAL), 1) || '%' AS SAL_COMP,
	ROUND((100.0 * CML_AMT / TS.TOTAL), 1) || '%' AS TTL_COMP,
	CASE
        WHEN ROUND((100.0 * CML_AMT / TS.TOTAL), 1) <= 40 THEN 'A'
        WHEN ROUND((100.0 * CML_AMT / TS.TOTAL), 1) <= 80 THEN 'B'
        ELSE 'C'
	    END AS 'RANK'
FROM
    RUISEKI
CROSS JOIN
    TOTAL_SALES AS TS
INNER JOIN 
    ITEM ON ITEM.ITEM_CODE == CODE
ORDER BY
    SAL_AMT DESC,
    CODE DESC