-- 売上日(SALES_DATE)の期間は2023年6月1日から2023年6月30日の1カ月間
-- 商品ごとの売上金額（降順）
WITH SALES_AMT AS (
    SELECT
        SALES_DTL.ITEM_CODE
        , MAX(SALES_DTL.UNITPRICE) * SUM(SALES_DTL.SALES_QTY) AS ITEM_SALES_AMT
    FROM
        SALES
    INNER JOIN
        SALES_DTL ON SALES.SALES_NO = SALES_DTL.SALES_NO
    WHERE
        SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY 
        SALES_DTL.ITEM_CODE
    ORDER BY 
        ITEM_SALES_AMT DESC
)
-- 累積売上金額、売上構成比、累積売上構成比
, SALES_COMPOSITION AS (
    SELECT
        sub.ITEM_CODE
        , sub.ITEM_SALES_AMT
        , SUM(sub.ITEM_SALES_AMT) OVER(
                ORDER BY
                    SALES_COMPOSITION_RATE DESC)
            AS ITEM_SALES_CUMULATIVE_AMT
        , ROUND(sub.SALES_COMPOSITION_RATE, 1) AS SALES_COMPOSITION_RATE
        , ROUND(SUM(sub.SALES_COMPOSITION_RATE) OVER(
                ORDER BY
                    SALES_COMPOSITION_RATE DESC)
            , 1)
            AS SALES_COMPOSITION_CUMULATIVE_RATE
    FROM (
        SELECT
            ITEM_CODE
            , ITEM_SALES_AMT
            , ITEM_SALES_AMT * 100.0 / SUM(ITEM_SALES_AMT) OVER() AS SALES_COMPOSITION_RATE
        FROM
            SALES_AMT
    ) AS sub
)
-- 商品ごとの累積売上構成比によりランクA～Cに分類
SELECT
    SC.ITEM_CODE AS CODE
    , ITEM.ITEM_NAME AS NAME
    , SC.ITEM_SALES_AMT AS SAL_AMT
    , SC.ITEM_SALES_CUMULATIVE_AMT AS CML_AMT
    , SC.SALES_COMPOSITION_RATE || '%' AS SAL_COMP
    , SC.SALES_COMPOSITION_CUMULATIVE_RATE || '%' AS TTL_COMP
    , CASE
        WHEN SC.SALES_COMPOSITION_CUMULATIVE_RATE <= 40 THEN 'A'
        WHEN SC.SALES_COMPOSITION_CUMULATIVE_RATE <= 80 THEN 'B'
        ELSE 'C'
        END AS 'RANK' 
FROM
    SALES_COMPOSITION AS SC
INNER JOIN
    ITEM ON SC.ITEM_CODE = ITEM.ITEM_CODE
;