--各商品の売上金額の集計
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'
),
-- 売上構成比、類型構成比の計算
COMP AS (
    SELECT
        CODE,
        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
    FROM
        RUISEKI
    CROSS JOIN
        TOTAL_SALES AS TS
)
-- RANKの計算
SELECT
    CODE,
    ITEM_NAME AS NAME,
    SAL_AMT,
    CML_AMT,
    SAL_COMP || '%' AS SAL_COMP,
    TTL_COMP || '%' AS TTL_COMP,
    CASE
        WHEN TTL_COMP <= 40 THEN 'A'
        WHEN TTL_COMP <= 80 THEN 'B'
        ELSE 'C'
	    END AS 'RANK'
FROM
    COMP
INNER JOIN 
    ITEM ON ITEM.ITEM_CODE == CODE
ORDER BY
    SAL_AMT DESC,
    CODE DESC